Re: my sqlite code is slow :(
I'll add this to the things I need to try, but that'd need quite a fancy values management, as to not insert the wrong thing into the wrong insert. Also I'm currently on a different project ;) Thanks Björnke On 26 Jun 2009, at 07:35, Bill Marriott wrote: Does SQLite support multi-line SQL statements via revExecuteSQL? You might try assembling the whole operation in a variable, BEGIN INSERT INSERT INSERT ... COMMIT and see how that flies ___ 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 ___ 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
Re: my sqlite code is slow :(
Does SQLite support multi-line SQL statements via revExecuteSQL? You might try assembling the whole operation in a variable, BEGIN INSERT INSERT INSERT ... COMMIT and see how that flies ___ 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
Re: my sqlite code is slow :(
The SQLite database is local. A code that writes a similar amount of data into text files (where each file would be a record) is too fast to bother with any user feedback (at least on this machine). As for not using an array, I haven't tried that yet, good idea. I'll try tomorrow, but i have multiline data and in addition, some fields might contain single quotes and other intervening stuff. So I'd need to introduce lots of escaping and data changing things to use a single string. thanks for your thoughts, everybody björnke PS: and yes i too think that something must be wrong somewhere On 23 Jun 2009, at 01:54, Sarah Reichelt wrote: 2009/6/23 Björnke von Gierke : Well, i think i tried everything suggested, and then some... It seems that to create 1 mb of sqlite data, rev takes approximately half a minute, no matter what one does to speed it up. too bad. Without knowing much about it, I wonder is the problem creating the query from an array? I presume you are using the :1 :2 method of filling in the elements in a query. I have never used this but have always assembled my SQL queries as a complete string before executing. Could this be causing the slow-down? Is the SQLite database file on the local drive or on a networked drive i.e. is it possible that transfer speed is an issue? I haven't used SQLite much and even then, not for large amounts of data, but I have found it to be very fast, so I feel there has to be something wrong somewhere. Cheers, Sarah ___ 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 ___ 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
Re: my sqlite code is slow :(
2009/6/23 Björnke von Gierke : > Well, i think i tried everything suggested, and then some... > > It seems that to create 1 mb of sqlite data, rev takes approximately half a > minute, no matter what one does to speed it up. too bad. > Without knowing much about it, I wonder is the problem creating the query from an array? I presume you are using the :1 :2 method of filling in the elements in a query. I have never used this but have always assembled my SQL queries as a complete string before executing. Could this be causing the slow-down? Is the SQLite database file on the local drive or on a networked drive i.e. is it possible that transfer speed is an issue? I haven't used SQLite much and even then, not for large amounts of data, but I have found it to be very fast, so I feel there has to be something wrong somewhere. Cheers, Sarah ___ 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
Re: my sqlite code is slow :(
Well, i think i tried everything suggested, and then some... It seems that to create 1 mb of sqlite data, rev takes approximately half a minute, no matter what one does to speed it up. too bad. On 22 Jun 2009, at 11:05, viktoras d. wrote: 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? -- official ChatRev page: http://bjoernke.com/runrev/chatrev.php Chat with other RunRev developers: go stack URL "http://bjoernke.com/stacks/chatrev/chatrev1.3b3.rev"; ___ 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
Re: my sqlite code is slow :(
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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: my sqlite code is slow :(
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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: my sqlite code is slow :(
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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: my sqlite code is slow :(
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
my sqlite code is slow :(
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 -- official ChatRev page: http://bjoernke.com/runrev/chatrev.php Chat with other RunRev developers: go stack URL "http://bjoernke.com/stacks/chatrev/chatrev1.3b3.rev"; ___ 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
