my sqlite code is slow :(

[Björnke von Gierke]

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


-- 

official ChatRev page:
http://bjoernke.com/runrev/chatrev.php

Chat with other RunRev developers:
go stack URL "http://bjoernke.com/stacks/chatrev/chatrev1.3b3.rev"