Sign in to follow this  
Followers 0
Xwolf

SQLite the speed of INSERT

5 posts in this topic

It was too slow to insert data.

Can somebody help me speed it up.

Thanks in advance for any help. :)

Code

#include <sqlite.au3>
#include <sqlite.dll.au3>

_SQLite_Startup()
_SQLite_Open("abc.db3")
_SQLite_Exec(-1,"Create table abc(id)")

For $i=1 To 100 Step 1
    _SQLite_Exec(-1,"Insert into abc values ('1')")
    ConsoleWrite($i & @CRLF)
Next

_SQLite_Close ("abc.db3")
_SQLite_Shutdown ()

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

I think enclosing the loop with a TRANSACTION statements should improve speed... also try to skip the consolewrite :)... Additionally you can set a Pragma command directly after the creation of the DB which is said (?) to speed up the DB. Look-up sqlite.org docu for explanation...

#include <sqlite.au3>
#include <sqlite.dll.au3>

_SQLite_Startup()
_SQLite_Open("abc.db3")
_SQLite_Exec(-1, "PRAGMA synchronous = OFF;")
_SQLite_Exec(-1,"Create table abc(id)")

_SQLite_Exec(-1, "BEGIN TRANSACTION")
For $i=1 To 100 Step 1
    _SQLite_Exec(-1,"Insert into abc values ('1')")
Next
_SQLite_Exec(-1, "COMMIT TRANSACTION")

_SQLite_Close ("abc.db3")
_SQLite_Shutdown ()

That gave me

+>14:00:17 AutoIT3.exe ended.rc:0
+>14:00:18 AutoIt3Wrapper Finished
>Exit code: 0   Time: 1.968

and don't forget that this included the creation of the DB and the shutdown.

Edited by KaFu

Share this post


Link to post
Share on other sites

Thank you very much! KaFu

This is my result when using your code.

+>21:21:10 AutoIT3.exe ended.rc:0
+>21:21:11 AutoIt3Wrapper Finished
>Exit code: 0    Time: 2.174

I will think about your code.

Thanks again for your help. :)

Share this post


Link to post
Share on other sites

Was just curious what measured effect the COMMIT has...

#include <sqlite.au3>
#include <sqlite.dll.au3>

_SQLite_Startup()
_SQLite_Open("abc.db3")
_SQLite_Exec(-1, "PRAGMA synchronous = OFF;")
_SQLite_Exec(-1,"Create table abc(id)")


$begin = TimerInit()
_SQLite_Exec(-1, "BEGIN TRANSACTION")
For $i=1 To 1000 Step 1
    _SQLite_Exec(-1,"Insert into abc values ('1')")
Next
_SQLite_Exec(-1, "COMMIT TRANSACTION")
ConsoleWrite(@crlf & "With COMMIT " & round(TimerDiff($begin),0) & " ms" & @crlf)

$begin = TimerInit()
For $i=1 To 1000 Step 1
    _SQLite_Exec(-1,"Insert into abc values ('1')")
Next
ConsoleWrite(@crlf & "Without COMMIT " & round(TimerDiff($begin),0) & " ms" & @crlf & @crlf)

_SQLite_Close ("abc.db3")
_SQLite_Shutdown ()

Result

With COMMIT 116 ms

Without COMMIT 682 ms

+>14:54:30 AutoIT3.exe ended.rc:0
+>14:54:31 AutoIt3Wrapper Finished
>Exit code: 0   Time: 2.167

Clearly shows the speed improvement of the commit... and that most time is spend creating db, initiated connection and shut it down afterwards. The insert part seems to be by far the fastest, note that I increased the inserts to 1000!

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

:) Yes KaFu

I am also confusing about "atomic commit".

Edited by Xwolf

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
Sign in to follow this  
Followers 0