||Re: complex indexes vs index key fields
Mervyn Bick <email@example.com>
||Wed, 29 Jul 2020 16:24:44 +0200
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
> 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
s = seconds()
d = new adodatabase()
d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata"
d.active = true
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))"
?e.message, 'Table exists'
_app.allowYeildOnMsg = true
f = new file()
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)+"),"
if n%500 = 0
cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma
d.active = false
? n,' Records added '
//Excludes site field from the INSERT. Wraps siteID and eTimestamp in single quotes
//Adds NULL in empty fields in the .csv file.
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'
//Reformats the timestamp as YYYY-MM-DD HH:MM:SS
if not 'A'$cTs and not 'P'$cTs
cRet = cTs
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
cH = substr(cTs,10,2)
cM = substr(cTs,13,2)
cS = ":00',"
cRet = cY+cH+':'+cM+cS