Slow write to sqlite on usb sticks

[Terry Judd]

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 <terry.judd at unimelb.edu.au<mailto:terry.judd at unimelb.edu.au>>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
use-livecode at lists.runrev.com<mailto:use-livecode at lists.runrev.com>
Please visit this url to subscribe, unsubscribe and manage your
subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
use-livecode at lists.runrev.com<mailto:use-livecode at lists.runrev.com>
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode