Hi,
I am doing the following with a non-ui interface running
Step 3 was quite slow taking around 140 seconds per batch of data, so I thought ahaa what we need is an in-memory db.
So I changed my temp table from disk to in-memory. Under the debugger it was still taking 80 to 100 seconds to insert the 4000 records. This didn't seem right.
I then ran the code in release mode and voila it flies taking only seconds to insert the same data. Much better and more like the expectation.
I am using synchronous queries as I cannot get async queries to work in this environment although I might have another go at this using the in-mem db.
The relevant code is:
Is there an explanation for this behaviour? I haven't come across this difference in speed between the debug mode and release.
I am doing the following with a non-ui interface running
- - Extract about 4000 records from an sqLite table into a list
- - Iterate thru the list and add some data
- - Insert this data into a temporary table
- - Aggregate the data out of the temp table into the final output table (reduction in no of records ~ 150:1).
Step 3 was quite slow taking around 140 seconds per batch of data, so I thought ahaa what we need is an in-memory db.
So I changed my temp table from disk to in-memory. Under the debugger it was still taking 80 to 100 seconds to insert the 4000 records. This didn't seem right.
I then ran the code in release mode and voila it flies taking only seconds to insert the same data. Much better and more like the expectation.
I am using synchronous queries as I cannot get async queries to work in this environment although I might have another go at this using the in-mem db.
The relevant code is:
In-Memory Database:
'============================================================================================
Private Sub createInMemDb(sqlArg As SQL)
If sqlArg.IsInitialized = False Then
sqlArg.InitializeSQLite("", ":memory:", True)
Dim qryStr As String = _
$"CREATE TABLE tblAgg2SecTemp (
julianDateUTC REAL,
jDate DATETIME,
JdateTime5Min REAL,
dataTicks BIGINT,
frequency REAL,
Vrms REAL,
P1 REAL,
P2 REAL,
P3 REAL,
ProcessorTemp REAL,
mainBatteryVolts REAL,
Quality REAL,
serverTicksStart BIGINT,
serverTicksEnd BIGINT,
N5 INTEGER,
dataTicksString STRING,
JdateTime5MinStr DATETIME
);"$
sqlArg.ExecNonQuery(qryStr)
End If
End Sub
'============================================================================================
Private Sub openAggTemp_DB(setJournalMode As Boolean, inMemory As Boolean) As Boolean
errRec.fcnName = "openAggTemp_DB"
If inMemory = True Then
createInMemDb(sqlDbAggTemp)
dbStatus.sqlDbAggTempInMemory = True
Else
Try
sqlDbAggTemp.InitializeSQLite(DbAggTemp_pathName, DbAggTemp_fileName, False)
dbStatus.sqlDbAggTemp_Open = True
dbStatus.sqlDbAggTempInMemory = False
Catch
closeAllDataBases
Dim LogMsgStr As String = "Couldn't Open - " & DbAgg_pathName & " " &DbAggTemp_fileName
setErrRecAndSave(3, -1, -1, LogMsgStr, LastException, True)
Return False
End Try
If setJournalMode Then Return setDbToWAL(sqlDbAggTemp)
End If
Return True
End Sub
'============================================================================================
'This code takes minutes to run in debug and a couple of seconds to run in release mode
private Sub insertRecsToTempBuffer(recList As List, tblTempMetaDataArg As tblTemp_Meta_t, N5Arg As Int)
Dim n As Int = 1
Dim listN As Int
listN = recList.Size
Dim jds As jdWithString_t
jds.Initialize
Dim qryStr As String
'test the data set to ensure all records for the five minute time windows will fit in memory
qryStr = _
$"INSERT INTO tblAgg2SecTemp(
julianDateUTC,
jDate,
JdateTime5Min,
dataTicks,
frequency,
Vrms,
P1,
P2,
P3,
ProcessorTemp,
mainBatteryVolts,
Quality,
serverTicksStart,
serverTicksEnd,
N5,
dataTicksString,
JdateTime5MinStr)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"$
tblTempMetaData.startTimeProcess = DateTime.Now
Log(" ")
Log("Insert raw data into Agg Temp = " & DateTime.Date(tblTempMetaData.startTimeProcess) & " " & DateTime.Time(tblTempMetaData.startTimeProcess))
'Set up the arguments - values to be inserted
For Each row() As String In recList
jds = build5MinuteDateTimeStr(row(0))
Dim ArgArray As List
ArgArray.Initialize
ArgArray.Add(Round2(row(0), 5)) 'julianDateUTC
ArgArray.Add(row(1)) 'jDate
ArgArray.Add(jds.jd) 'JDateTime5Min
ArgArray.Add(jds.ticks) 'dataTicks
ArgArray.Add(row(4)) 'frequency
ArgArray.Add(row(5)) 'Vrms
ArgArray.Add(row(6)) 'P1
ArgArray.Add(row(7)) 'P2
ArgArray.Add(row(8)) 'P3
ArgArray.Add(row(9)) 'ProcessorTemp
ArgArray.Add(row(10)) 'mainBatteryVolts
ArgArray.Add(row(11)) 'Quality
ArgArray.Add(row(12)) 'serverTicksStart
'If we are at the last records then add in the data time - don't do this for all the query is slow
If n = listN Then
'Add the time to the last record
ArgArray.Add(DateTime.Now) '13 serverTicksEnd
Else
ArgArray.Add(0) '13 serverTicksEnd
End If
ArgArray.Add(N5Arg) '14 N5
ArgArray.Add(DateTime.Date(row(3)) & " " & DateTime.Time(row(3))) '15 dataTicksString
ArgArray.Add(jds.jdStr) '16 JdateTime5MinStr
' openAggTemp_DB(False, True)
'Insert transaction
Try
sqlDbAggTemp.BeginTransaction
sqlDbAggTemp.ExecNonQuery2(qryStr, ArgArray)
sqlDbAggTemp.TransactionSuccessful
n = n + 1
Catch
tblTempMetaData.errorFlag = False
Dim LogMsgStr As String = "Insert records into AggTemp query failed"
setErrRecAndSave(8, listN, -1, LogMsgStr, LastException, True)
closeAllDataBases
End Try
Next ...Is there an explanation for this behaviour? I haven't come across this difference in speed between the debug mode and release.
