Writing in SQLite table. How to do it faster?

[Suppir]

Hello.

Check this code:

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

Global $DB
Global $starttime = _Timer_Init()

_SQLite_Startup ()

$DB = _SQLite_Open("sqlite.db")
_SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);")

For $i = 1 to 1000 Step 1
    _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');")
Next

MsgBox(0, "", _Timer_Diff($starttime))

On my PC it takes about 80 seconds. It too slow for writing only 20 kb of data in database. Where is the problem and how to do it faster? Thanks.

Edited February 9, 2010 by Suppir

[whim]

I would start off by timing DB creation and data insertion separately -

maybe it's just the creation that takes a long time ?

[Suppir]

I would start off by timing DB creation and data insertion separately -

maybe it's just the creation that takes a long time ?

No, the base is created in a moment, and then veeeery slowly grows up to 20 kb. I think the problem in slow INSERT.

Edited February 9, 2010 by Suppir

[Suppir]

OK, I've got it.

I should write this way:

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

Global $DB
_SQLite_Startup ()
$DB = _SQLite_Open("sqlite.db")
_SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);")
_SQLite_Exec($DB, "BEGIN;")

Global $starttime = _Timer_Init()

For $i = 1 to 1000 Step 1
    _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');")
Next
_SQLite_Exec($DB, "COMMIT;")

MsgBox(0, "", _Timer_Diff($starttime))

Time = 0,3 sec. It 270 times faster then first code.

In this way we starting transaction, then putting inside data, then closing transaction.

[PsaltyDS]

OK, I've got it.

I should write this way:

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

Global $DB
_SQLite_Startup ()
$DB = _SQLite_Open("sqlite.db")
_SQLite_Exec($DB, "CREATE TABLE TABLE1 (Text);")
_SQLite_Exec($DB, "BEGIN;")

Global $starttime = _Timer_Init()

For $i = 1 to 1000 Step 1
    _SQLite_Exec($DB, "INSERT INTO TABLE1 VALUES ('Some text');")
Next
_SQLite_Exec($DB, "COMMIT;")

MsgBox(0, "", _Timer_Diff($starttime))

Time = 0,3 sec. It 270 times faster then first code.

In this way we starting transaction, then putting inside data, then closing transaction.

Nice, learned something today!

[jchd]

I didn't catch this thread before.

As a rule of thumb, one should always wrap bulk inserts inside a transaction. SQLite is ACID (look here) and that means that individual inserts need to be committed to disk individually. That translates into several to many writes for a single insert. Typical rate for stock IDE/SATA 7200 rpm disk is about 12 TPS (transaction per second)or lower.

By default, SQLite uses auto-commit: each operation is wrapped inside an internally-generated transaction.

Now when you group your inserts into an explicit transaction, the required writes are merged and only applied by the time the COMMIT is executed. The effect is a dramatic improvement in speed, just as you experienced.

As a sidenote, if ever your database is shared and if other process(es) are possibly reading/writing the database asynchronously, then you should use BEGIN IMMEDIATE / COMMIT. This has to do with the type of locks that are created / held, and directly effects the moments the lock is applied.

Lastly, there is little difference for a memory-based database as the write rate is no more bounded by disk rotation speed.