my sqlite code is slow :(

[Björnke von Gierke]

It seems that revcommitdatabase is doing the commit part, but not the  
begin part, and therefore it's utterly useless, am I seeing that right?

I tried to use begin/commit transaction, but it seemed to slow down  
even more. but i didn't experiment with that aproach much, so I  
understand that I am on the right track, and should be able to speed  
it up, if I'd use begin/commit correctly.


On 22 Jun 2009, at 09:12, viktoras d. wrote:

> 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
>>
>>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution