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