Wow, thanks Pete - I'll definitely give this method a try. Terry..
On 27/04/2012, at 3:38 AM, "Peter Haworth" <[email protected]> wrote: > Hi Terry, > You can still do individual INSERTS, just need a few lines of extra code at > the start and end of your code. Here's an amended version of your code > with the BEGIN/ROLLBACK/COMMIT in place. > > put the cUpdateMessagesSQL of this cd into tSQL # my insert statement > put revOpenDatabase("sqlite", pLocalPath, , , , ) into connectionID > > --- Start an sqlite transaction > revExecuteSQL connectionID,"BEGIN TRANSACTION" > > repeat for each line tRecord in pData # 1 message per line > # do some formatting stuff here > put merge(tSQL) into tSQL1 > revExecuteSQL connectionID, tSQL1 > > --Check for an error and back out the INSERTS > if the result is not an integer then > revExecuteSQL connectionID,"ROLLBACK" > -- Do whatever error tidy up is necessary here > exit top > end if > > end repeat > > -- Commit the INSERTs to disk > revExecuteSQL connectionID,"COMMIT" > > revCloseDatabase connectionID > > SQLIte automatically starts/commits a transaction around individual > statements if you haven't explicitly defined one with a BEGIN statement. > There's disk i/o overhead involved in committing a transaction to disk and > in your case, that overhead happens 40 times. With the BEGIN/COMMIT in > place, it only happens once and since the performance problem seems to be > related to the slow transfer speed of USB drives, that should help. > > The error check/ROLLBACK code will result in none of your INSERTs being > written to disk if an error occurs on any of them. With the code you have > right now, if you got an error on an INSERT, you'd be left with the INSERTs > processed before the error on disk. Depending on your application, that > may or may not be a problem. If you'd rather leave the successful INSERTs > in the database, remove the ROLLBACK line and change the "exit top" to > "exit repeat", that way the COMMIT at the end of the repeat will still be > executed. > > If you have any indexes defined on columns in the table you're inserting > into, that will incur extra disk overhead too. Indexes are great for fast > retrieval and normally their overhead during INSERTs isn't an issue but it > seems that using a USB drive magnifies the effect of every disk i/o so that > could be a problem. Have you tried SELECTing data from this table yet? > How does that performance compare? > > As far as INSERTing multiple rows at the same time, SQLite recently > enhanced the INSERT command to allow that. You can specify multiple sets > of column values with the VALUES clause: > > INSERT INTO tableA (Col1,Col2) VALUES ('abc','def'),('ghi',jkl'), etc > > I haven't experimented with that syntax yet to know if it provides any > performance improvements. I don't remember which version of SQLite that > was introduced in so it's possible that Livecode doesn't include the > required version of the SQLite library. > > There are other things you can do to reduce i/o but I suggest you try the > above first and see if it makes any difference. > > Pete > lcSQL Software <http://www.lcsql.com> > > > > On Wed, Apr 25, 2012 at 11:20 PM, Terry Judd <[email protected]>wrote: > >> >> On 26/04/2012, at 3:59 PM, Peter Haworth wrote: >> >> Hi Terry, >> Are you wrapping all 40 (or however many INSERTS) into one transaction? By >> which I mean there should be a BEGIN before the repeat loop starts and a >> COMMIT after it finishes. That definitely helps with performance and also >> allows you to ROLLBACK if any of the INSERTs fail. >> >> Hi Pete - thanks for the suggestion, at the moment I'm doing 40 separate >> inserts, like this... >> >> put the cUpdateMessagesSQL of this cd into tSQL # my insert statement >> put revOpenDatabase("sqlite", pLocalPath, , , , ) into connectionID >> repeat for each line tRecord in pData # 1 message per line >> # do some formatting stuff here >> put merge(tSQL) into tSQL1 >> revExecuteSQL connectionID, tSQL1 >> end repeat >> revCloseDatabase connectionID >> >> I doubt that would make fix the huge timing difference but worth a try if >> you're not already doing it. >> >> Yes, submitting them all at once could well help. I'm no SQL expert >> though. How do I format the data and statement so that I'm submitting >> multiple records at once? >> >> Terry... >> >> Only other thing I can think of is to try different combinations of LC and >> the db on disk/USB - might narrow down which of the two is causing the >> problem. >> >> Pete >> lcSQL Software <http://www.lcsql.com> >> >> >> >> On Wed, Apr 25, 2012 at 6:57 PM, Terry Judd <[email protected] >> <mailto:[email protected]>>wrote: >> >> We're finding that it's very slow to write even small amounts of data from >> Livecode to a sqlite file when the Livecode app and the db file are on a >> usb stick (the db stores a local copy of the user's messages as they are >> downloaded from a server). It seems to be something that is peculiar to the >> usb setup as it works very quickly when run from a hard drive (less that 1 >> second for 40 messages vs 20-30 seconds on the usb stick). Has anyone >> experienced anything similar and/or does anyone have any suggestions as to >> how we might speed the process up? >> >> I'm currently downloading the messages (as xml), converting them to an >> array (from, to, subject, body, attachments etc.) and then writing each to >> the sqlite db within a repeat loop and then closing the db connection. >> >> Terry... >> >> _______________________________________________ >> use-livecode mailing list >> [email protected]<mailto:[email protected]> >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> _______________________________________________ >> use-livecode mailing list >> [email protected]<mailto:[email protected]> >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> >> _______________________________________________ >> use-livecode mailing list >> [email protected] >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> > _______________________________________________ > use-livecode mailing list > [email protected] > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
