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