SQLite in memory DB inserts too slow

[Rodger]

Hello,

I have the following code:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Date.au3>

Local $hQuery, $aRow, $hMemDb

If @OSArch = "X86" Then
_SQLite_Startup ("SQLite3.dll")
EndIf

If @OSArch = "X64" Then
_SQLite_Startup ("SQLite3-64.dll")
EndIf

$hMemDb = _SQLite_Open(":memory:") ; Creates a :memory: database
If @error Then
    ConsoleWrite("Can't create a memory Database!" & @CRLF)
    Exit $hMemDb
EndIf
_SQLite_Exec($hMemDb,"PRAGMA journal_mode = OFF;")
_SQLite_Exec($hMemDb,"PRAGMA temp_store = MEMORY;")

$Query = "Create table FileName_LineNumber (Nr Number, T1 Text, T2 Text);"
_SQLite_Exec($hMemDb,$Query)
If @error Then
    ConsoleWrite("Table" & @CRLF)
    Exit $hMemDb
EndIf

Dim $HH, $MM, $SS
$StartTime = _NowCalc()
_SQLite_Exec($hMemDb,"BEGIN TRANSACTION;")
For $i = 1 to 1000000
    $Insert = "Insert into FileName_LineNumber values(" & $i & ",'Test1', 'Test2');"
    _SQLite_Exec($hMemDb,$Insert)
    If @error Then
        ConsoleWrite("Insert" & @CRLF)
        Exit $hMemDb
    EndIf
Next
_SQLite_Exec($hMemDb,"COMMIT TRANSACTION;")
;Time Elapsed
$EndTime = _NowCalc()
$SS = _DateDiff('s', $StartTime, $EndTime)
_TicksToTime($SS * 1000, $HH, $MM, $SS)
ConsoleWrite("Time Elapsed : " & StringFormat("%02i:%02i:%02i", $HH, $MM, $SS) & @CRLF)

Loading de table with 1000000 inserts takes at least 2.5 minutes to complete. A look already for topics about it in the forum but I can't get it faster.

Any idea's ?

[jchd]

You should used "chained insertion" like this:

insert into XYZ values (...), (...), (...), ...

The more rows  you insert at once the faster the insertion is (that's untrue literally but at least you get a significant speed increase).

Your initial script took > 5 minutes on my slow PC but this version take 15 seconds.

expandcollapse popup

#include <SQLite.au3>
;~ #include <SQLite.dll.au3>    ; download the most recent suitable DLL once and comment this out
#include <Date.au3>

Local $hQuery, $aRow, $hMemDb

_SQLite_Startup()
; @OSArch isn't the macro you would use here
; also not mentionning the DLL full name is easier
;~ If @AutoItX64 Then
;~  _SQLite_Startup()   ; "SQLite3-64.dll")
;~ Else
;~  _SQLite_Startup()   ; "SQLite3.dll")
;~ EndIf

$hMemDb = _SQLite_Open()    ; :memory: is by default ; Creates a :memory: database
If @error Then
    ConsoleWrite("Can't create a memory Database!" & @CRLF)
    Exit
EndIf
; these are default for a :memory: DB
;~ _SQLite_Exec($hMemDb, "PRAGMA journal_mode = OFF;")
;~ _SQLite_Exec($hMemDb, "PRAGMA temp_store = MEMORY;")

$Query = "Create table FileName_LineNumber (Nr integer, T1 Text, T2 Text);"
_SQLite_Exec($hMemDb, $Query)
If @error Then
    ConsoleWrite("Table" & @CRLF)
    Exit    ; meaningless to assign the DB handle to return code
EndIf

Local $HH, $MM, $SS
Local $StartTime = TimerInit()
_SQLite_Exec($hMemDb, "BEGIN")  ; implicit TRANSACTION;")
Local $iLimit = 1000000
Local $iChunk = 200
Local $sSQL = "Insert into FileName_LineNumber values "
Local $Insert = $sSQL
For $i = 1 To $iLimit
    $Insert &= "(" & $i & ",'Test1','Test2')"
    If Mod($i, $iChunk) Then
        $Insert &= ","
    Else
        _SQLite_Exec($hMemDb, $Insert)
        $Insert = $sSQL
    EndIf
; essentially useless
;~  If @error Then
;~      ConsoleWrite("Insert" & @CRLF)
;~      Exit $hMemDb
;~  EndIf
Next
_SQLite_Exec($hMemDb, "COMMIT") ; TRANSACTION;")
;Time Elapsed
Local $TimeSpent = TimerDiff($StartTime)
_TicksToTime($TimeSpent, $HH, $MM, $SS)
ConsoleWrite("Time Elapsed : " & StringFormat("%02i:%02i:%02i", $HH, $MM, $SS) & @CRLF)

Note that the same script took no more than 16 seconds when using a disk-based (non SSD) DB.

For real-world applications, beware that SQLite SQL command has some size limit that you shouldn't exceed. So don't try to insert 500000 values at a time!

[Rodger]

Thank you very much.

I didn't know about  "chained insertion"; it's much faster this way 

I will keep your warning about size limits in mind.