lsakizada

Slow Insert to sqlite db

8 posts in this topic

#1 ·  Posted (edited)

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.

#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 by lsakizada

Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

_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 by mpower

Share this post


Link to post
Share on other sites

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

_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!!!!  :huggles:

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 :bye:


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now