Hi, Björnke, SQLite by default is in autocommit mode when you open a database. That means that a commit is executed after each INSERT INTO statement. This makes bulk inserts into a table very slow.
To bring it out of autocommit mode modify your code to do a 'revExecuteSQL("BEGIN TRANSACTION")' before you start to insert and a 'revExecuteSQL("END TRANSACTION")' after you are finished with inserting. See here for more information: http://www.sqlite.org/lang_transaction.html http://www.sqlite.org/lang_transaction.html I hope that is the reason for the slowness. Best regards, Florian 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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution