| Subject | Re: complex indexes vs index key fields |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Fri, 31 Jul 2020 10:44:49 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | create_all_timestamps.prg |
On 2020-07-30 08:09, Gaetano De Luisi wrote: > > Hi Mervyn, > > Brilliant, it took only 10.62 seconds to load the 25k records! > > Thanks for that sample code! You will also need a table of all the possible timestamps to check for missing timestamps in your data. The attached program should take longer than that (but probably under a minute) to create a year's worth of entries as it has to do the INSERT 106 times. On the other hand it only needs to be run once a year so this shouldn't be a problem. RIGHT JOINing the table to the energydata table for a given time span will tell you if there are any missing timestamps. It will, however, not tell which siteID the missing timestamp belongs to. If there are any missing timestamps you will need to select the records for each siteID in turn and RIGHT JOIN the table. This will identify any missing records for that specific site. SOD's Law decrees that the missing records will always be in the last site(s) tested. :-) 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 all_timestamps ( " cStr += " eTimestamp TIMESTAMP(6) NOT NULL," cStr += " PRIMARY KEY USING BTREE (eTimestamp))" d.ExecuteSQL(cStr) catch(exception e) ?e.message, 'Table exists' endtry nYear = 2020 //edit for following years nMonth = 0 nDay = 1 nHour = 0 nMin = 0 dDate = new date(nYear,nMonth,nDay,nHour,nMin,0) dEndDate = new date(nYear+1,nMonth,nDay,nHour,nMin,0) _app.allowYeildOnMsg = true n = 0 nInsert = 1 do while dttoc(dDate) < dttoc(dEndDate) n1 = 1 cStr = "insert into all_timestamps (eTimestamp) values " do while n1 < 1000 and dttoc(dDate) < dttoc(dEndDate) cStr += "('"+dDate.getYear()+"-"+str((dDate.getMonth()+1),2,0,'0')+"-" cStr += str(dDate.getDate(),2,0,'0')+" " cStr += str(dDate.getHours(),2,0,'0')+":"+str(dDate.getMinutes(),2,0,'0')+":00')," nMins = dDate.getminutes() if nMin < 55 nMin += 5 else nMin = 0 if nHour < 23 nHour ++ else nHour = 0 nDay ++ endif endif dDate.setYear(nYear) dDate.setMonth(nMonth) dDate.setDate(nDay) dDate.setHours(nHour) dDate.setminutes(nMin) _app.executeMessages() n++ n1++ enddo cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma ?'insert',nInsert+' of 106' nInsert ++ d.ExecuteSQL(cStr) enddo d.ExecuteSQL('commit') d.active = false ? n,' Records added ' ?'seconds',(seconds()-s) | |