Slow Insert to sqlite db

[lsakizada]

Hi,

This is a question about sqlite.

I have a large size 2 dimension array that I want to insert it into physical sqlite database table (not in memory).

My problem is that it is take very long time.

How do I make it faster?

BTW: On my computer its take 92 seconds!

 

Insert to database took 92.2876927154525 seconds!

 

Here is a code sample to assist me.

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Local $iRows, $iColumns, $aRes
Local $hDB = @ScriptDir & "\" & "Test.db"
If FileExists($hDB) Then FileDelete($hDB)
Local $aResult, $iRows, $iColumns, $iRval
;Local $aArray[5][2]
Local $hTimer, $fDiff

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf

$hDskDb = _SQLite_Open($hDB) 
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!")
    Exit -1
EndIf

Local $aArray[100][3]
For $i = 0 To UBound($aArray) - 1
    For $j = 0 To UBound($aArray, 2) - 1
        $aArray[$i][$j] = "Item" & $i * $j
    Next
Next

;_ArrayDisplay($aArray)

If Not _SQLite_Exec($hDskDb, "CREATE TABLE T (Column0, Column1,Column2);") = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())
$hTimer = TimerInit()
For $i = 0 To UBound($aArray) - 1
    _SQLite_Exec($hDskDb, "INSERT INTO T (Column0,Column1,Column2) VALUES ( " & _SQLite_FastEscape($aArray[$i][0]) & "," & _SQLite_FastEscape($aArray[$i][1]) & "," & _SQLite_FastEscape($aArray[$i][2]) & ");")
Next

$fDiff = TimerDiff($hTimer)
ConsoleWrite(@CRLF & "Insert to database took " & $fDiff / 1000 & " seconds!" & @CRLF)

_SQLite_GetTable2d($hDskDb, "SELECT * FROM T;", $aRes, $iRows, $iColumns)
_SQLite_Display2DResult($aRes) ; Output to Console

_SQLite_Close()
_SQLite_Shutdown()

Edited March 10, 2015 by lsakizada

[mpower]

Here you go, on my PC reduced from ~21 seconds to 0.015 seconds or almost instantaneous

_SQLite_Exec($hDskDb, "begin immediate;")
For $i = 0 To UBound($aArray) - 1
    _SQLite_Exec($hDskDb, "INSERT INTO T (Column0,Column1,Column2) VALUES ( " & _SQLite_FastEscape($aArray[$i][0]) & "," & _SQLite_FastEscape($aArray[$i][1]) & "," & _SQLite_FastEscape($aArray[$i][2]) & ");")
Next
_SQLite_Exec($hDskDb, "commit;")

Be sure to enclose your insert loop(s) within a transaction (BEGIN; ... COMMIT;) to speed up bulk inserts.

Source: '?do=embed' frameborder='0' data-embedContent>>

Edited March 10, 2015 by mpower

[lsakizada]

Here you go, on my PC reduced from ~21 seconds to 0.015 seconds or almost instantaneous

_SQLite_Exec($hDskDb, "begin immediate;")
For $i = 0 To UBound($aArray) - 1
    _SQLite_Exec($hDskDb, "INSERT INTO T (Column0,Column1,Column2) VALUES ( " & _SQLite_FastEscape($aArray[$i][0]) & "," & _SQLite_FastEscape($aArray[$i][1]) & "," & _SQLite_FastEscape($aArray[$i][2]) & ");")
Next
_SQLite_Exec($hDskDb, "commit;")

Be sure to enclose your insert loop(s) within a transaction (BEGIN; ... COMMIT;) to speed up bulk inserts.

Source: '?do=embed' frameborder='0' data-embedContent>>

 

@mpower

Get allot of these!!!! 

So simple pretty code! Exactly what I was needed! 

BTW: I saw this topic about backup DB from memory to file and I was consider it. '?do=embed' frameborder='0' data-embedContent>>

Now I do not need it

[mpower]

Glad to be of assistance, I actually learned a lot from your example and the link I included. I might actually start using SQL Lite in my scripts as it doesn't seem so scary anymore!

[jchd]

Go ahead and use it, it's a piece of cake to work with.

BTW you can group many more entries (e.g. few hundreds) in a "chained insert" statement. It's limited to SQLite statement string size limit but this value is quite large.

[mpower]

Thanks jchd, I am assuming you're the mastermind behind the SQLite UDF?

[jchd]

Not at all, I'm just an occasional maintainer. And yes I'm a big big fan of SQLite, even more with using a good third-party manager named SQLite Expert.

[mpower]

Thanks again jchd, will be downloading and playing around with the tool