Slow write to sqlite on usb sticks

Thu Apr 26 17:38:57 EDT 2012

Wow, thanks Pete - I'll definitely give this method a try.

Terry..

On 27/04/2012, at 3:38 AM, "Peter Haworth" <pete at lcsql.com> 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 <terry.judd at unimelb.edu.au>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 <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
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> 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
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
> 