my sqlite code is slow :(

[Florian von Walter]

Hi, Björnke,

SQLite by default is in autocommit mode when you open a database.
That means that a commit is executed after each INSERT INTO statement.
This makes bulk inserts into a table very slow.

To bring it out of autocommit mode modify your code to do a
'revExecuteSQL("BEGIN TRANSACTION")' before you start to insert and a
'revExecuteSQL("END TRANSACTION")' after you are finished with inserting.

See here for more information:
http://www.sqlite.org/lang_transaction.html
http://www.sqlite.org/lang_transaction.html

I hope that is the reason for the slowness.

Best regards,
Florian

Björnke von Gierke wrote:
> Hi persons that know database-fu
>
> I am trying to convert the xml docs into sqlite. It does work fine.
> However, it's also way too slow. the whole dictionary results in a
> roughly 7 MB large file. My code needs 3.5 minutes to parse the xml
> and create the file.
>
> I do this by using a repeat loop, and all the rev code is decently
> timed (around 1 milliseconds for all the steps, including my custom
> xml parsing). However, every time I insert an entry into the sqlite
> database, that line of code alone needs anything from 50 to 800
> milliseconds with a few extreme cases that take several seconds. I do
> know that insertions are not the speediest things to do with
> databases, but i think this is not normal, right?
>
> the code is a bit long to post here, but this is roughly what I found
> to be the fastest:
>
> 1. make sure file does not exist yet
> 2. create database connection (this automatically creates the file)
> 3. create table
> repeat for each loop start
> 4. create an array that contains all the data for the current entry
> (from xml)
> 5. insert into database
> 6. every 30th round (i also tested 10, 20, and 40), i issue
> "revCommitDatabase"
> end repeat
> 7. various cleanup
> 8. close database connection
>
>
> so... how can mass inserts into sql be made faster? I probably just
> misunderstood how this is supposed to work...
>
> thank you for your time
>
> Björnke
>
>