Sign in to follow this  
Followers 0
Suppir

Writing in SQLite table. How to do it faster?

6 posts in this topic

#1 ·  Posted (edited)

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 by Suppir

Share this post


Link to post
Share on other sites



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

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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Suppir

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.


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

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