Subject Re: complex indexes vs index key fields From Gaetano De Luisi <gaetanodd@hotmail.com> Date Thu, 30 Jul 2020 02:09:52 -0400 Newsgroups dbase.getting-started Hi Mervyn, Brilliant, it took only 10.62 seconds to load the 25k records! Thanks for that sample code! Cheers, Gaetano. Mervyn Bick Wrote: > On 2020-07-28 14:00, Mervyn Bick wrote: > > > Firebird does not accept multiple rows in a single INSERT but I've > > modified my loading program so that Gaetano can try it.  This program > > has NOT been tested as I don't have MySql available. > > There was a typo which has been fixed.  I've also added code to create > the table if it doesn't exist.  If the table exists the > try...catch...endtry will prevent an error.   The revised version is > attached. > > > > The program takes less than a second to generate the 8 INSERT statements > > requires to deal with the 7577 records.  Executing them should not take > > too many seconds.  :-) > > The program took 4.90 seconds to create the table and then load the 7576 > test records I have. > > I have created an ODBC connection named energydata  which has the > username and password hard-coded which accounts for the brief connection > string.  This will, of course, need to be changed. > > We've gone off-topic here so let's start a new thread in the ADO or the > sql-servers newsgroup. > > Mervyn. > > > > > clear > s = seconds() > d = new adodatabase() > d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata" > d.active = true > try >   cStr = "CREATE TABLE energydata ( " >   cStr += " siteID CHAR(6)  NOT NULL," >   cStr += "  eTimestamp TIMESTAMP(6) NOT NULL," >   cStr += "  egen FLOAT(10,4) DEFAULT NULL," >   cStr += "  econs FLOAT(10,4) DEFAULT NULL," >   cStr += "  ac FLOAT(10,4) DEFAULT NULL," >   cStr += "  pv_econs FLOAT(10,4) DEFAULT NULL," >   cStr += "  pv_eexp FLOAT(10,4) DEFAULT NULL," >   cStr += "  g_eimport FLOAT(10,4) DEFAULT NULL," >   cStr += "  PRIMARY KEY USING BTREE (siteID, eTimestamp))" >   d.ExecuteSQL(cStr) > catch(exception e) >   ?e.message, 'Table exists' > endtry > > > _app.allowYeildOnMsg = true > f = new file() > f.open('d:\examples\ado\energydata2.csv') > cRead = f.gets(1000) //header > n = 0 >  do while not f.eof() >    n1 = 1 >    cStr = "insert into energydata (siteId,eTimestamp,egen,econs,ac,pv_econs," >    cStr += "pv_eexp,g_eimport) values " >    do while n1 < 1000 and not f.eof() >      cRead = f.gets(10000) >      cStr += "("+parse_cRead(cRead)+")," >      _app.executeMessages() >      if n%500 = 0 >         ?n >      endif >      n++ >      n1++     >    enddo >    cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma >    ?'insert' >    d.ExecuteSQL(cStr) > enddo > d.ExecuteSQL('commit') > f.close() > d.active = false > ? n,' Records added ' > ?'seconds',(seconds()-s) > > function parse_cRead(cRead) >    //Excludes site field from the INSERT. Wraps siteID and eTimestamp in single quotes >    //Adds NULL in empty fields in the .csv file.   >    local cRet >    cRet = "'"+substr(cRead,1,at(',',cRead,1)-1)+"','" >    cTs = substr(cRead,at(',',cRead,2)+1,(at(',',cRead,3)-1)-at(',',cRead,2))  +"'," >    cRet += convert_ts(cTs) >    cRet += substr(cRead,at(',',cRead,3)+1) >    do while ",,"$cRet >       cRet = stuff(cRet,at(',,',cRet),2,",NULL,") >       if substr(cRet,len(cRet),1) = ',' >          cRet += 'NULL' >       endif   >    enddo   >    return cRet >     > function convert_ts(cTs) >    //Reformats the timestamp as YYYY-MM-DD HH:MM:SS >    local cRet,cY,cH,cM,cS >    if not 'A'$cTs and not 'P'$cTs >        cRet = cTs >    else >       cY = substr(cTs,1,9) >       cY = stuff(cy,7,0,'-') >       cY = stuff(cy,5,0,'-') >       if 'P'$cTs and val(substr(cTs,10,2)) = 12 >         cH = '00' >       elseif 'P'$cTs and val(substr(cTs,10,2)) < 12 >         cH = val(substr(cTs,10,2))+12 >       else >         cH = substr(cTs,10,2) >       endif         >       cM = substr(cTs,13,2) >       cS = ":00',"   >       cRet = cY+cH+':'+cM+cS >    endif >    return cRet >