my sqlite code is slow :(

viktoras d. viktoras at ekoinf.net
Mon Jun 22 03:12:42 EDT 2009


Hi, Bjoernke

Do your inserts within a transaction:
revExecuteSQL yourDbID, "BEGIN"
all inserts go here
revExecuteSQL yourDbID, "COMMIT"

Viktoras

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




More information about the use-livecode mailing list