B4J Question SQLite In-Memory table Very slow under debug - flies in release mode.

[rgarnett1955]

Hi,

I am doing the following with a non-ui interface running

1. - Extract about 4000 records from an sqLite table into a list
2. - Iterate thru the list and add some data
3. - Insert this data into a temporary table
4. - 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:

'============================================================================================
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.

 

[OliverA]

Is there an explanation for this behaviour?

It’s debug mode. Never judge performance by how something runs in debug mode