SQLite the speed of INSERT

[Xwolf]

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 ()

[KaFu]

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 January 22, 2009 by KaFu

[Xwolf]

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.

[KaFu]

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!

[Xwolf]

Yes KaFu

I am also confusing about "atomic commit".

Edited January 22, 2009 by Xwolf

[petenyc1]

Who would think that a programming post from 11 years ago could help someone.  But it did.  This improved the speed by 100x.  

Thanks!

[jchd]

If you need to further improve the insert speed, you can group multiple row values in each insert statement, all grouped inside a transaction.

insert into mytable values (1), (2), (3), ..., (100)

This cuts down by 100 (here) the number of times that the engine has to compile the statement and do its house keeping. For instance, you would only perform 10 inserts instead of 1000 in the example above.

[petenyc1]

First jchd thank you so much for your help.  Doing a project in Sqlite and most of the helpful posts are yours.  Especially in your signature the tutorials and help sites.   Thank you!!!!!!! 

Adding barcodes to our hospital supplies.  I downloaded a FDA database with 1.25m entries.  Loading into Sqlite.  First 250,000 took maybe 10-15 hours.  I made the changes above and the last 1,000,000 took 30mins!!!  I'm going to add your changes and see how quick I can get it done.  

Then the hospital gave me a list of 40,000 supplies that they use.  Going to go through list and search the sql table with the model number.  Will get the barcode ID (GTIN) and add to my list.  Purchasing will upload back and bam we'll have the ability to enter supplies, implants with barcodes.  Will be a huge time saver.  

Again thank you!!!!!!

 

 

[jchd]

For really massive changes to a table, it's often beneficial to drop all indices to said tables first, perform changes and only then rebuild indices.  Of course your mileage will vary depending on actual context and schema.

There are many possibilities to fine-tune a specific schema for a given use case and it's hard to give generic advices beyond basic guidelines.