Slow write to sqlite on usb sticks

[Peter Haworth]

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
>