my sqlite code is slow :(

[viktoras d.]

Hi, Björnke

make sure the "begin" and "commit" part is outside any loop, eg.

revExecuteSQL yourDbID, "BEGIN"
#loop starts here
repeat for each...
 revExecuteSQL yourDbID, "INSERT INTO table VALUES(....)"
end repeat
#loop ends here
revExecuteSQL yourDbID, "COMMIT"


Without begin-commit stated explicitly, SQLite automatically does 
begin-commit for each insert statement and this is why it slows down  to 
1 insert per 1 hard disk rotation :-).

Viktoras

Björnke von Gierke wrote:
> 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
>
> _______________________________________________
> 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
>