Re: [LiveCodeServer / on-Rev] SqlLite - Insert…. very slow

[Peter Haworth]

Hi Mathias,
You should warp your INSERT command in an SQLite transaction.  Before the
first one, revExecuteSQL tdatabaseID, "BEGIN TRANSACTION" and after the
last INSERT, rev$xecuteSQL tdatabaseID, "COMMIT".  You'll see dramatic
performance improvements, I can almost guarantee.

You should probably check for an error after each INSERT and if you find
one,revExecuteSQL tdatabaseID, "ROLLBACK".  The ROLLBACK will put your
database back into the state it was before any of the INSERTs were done,
assuming that's what you want to do if there is an error.

There's a couple of other things about the form of your INSERT statement
but try that first and see if that speeds things up.


Hope that helps,

Pete
lcSQL Software <http://www.lcsql.com>



On Sat, Jun 2, 2012 at 12:56 PM, Matthias Rebbe <
matthias_livecode_150811 at m-r-d.de> wrote:

> Hi,
>
> i have here a script which
>
> - creates a local  sqlLite DB
> - creates a Table with 13 fields
> - insert 3000 records from a textfile
>
> On my Mac this takes about 20 seconds. On an iPhone this take approx. 60
> seconds plus the download time for the 3000 line textfile.
> So i thought doing this on the server and let the iphone just download the
> gezipped sqlite file is much faster.
>
> So i tried this with livecode server on the On-Rev system. It takes about
> 4 minutes to run the script.
> I tried to do the sql inserts  one by one and also in segments of
>  10,50,100. Nothing improves the processing time.
>
> Is this a normal behaviour under livecode server? I thought this could be
> done in 2 or 3 seconds.
>
> My code looks like this
>
> put  "produkte.sqlite" into tDatabasePath
> put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
> repeat for each line l in tList
>  --tList contains 3000 lines with 13 items each
>  put "NSERT INTO produkte VALUES
> ('value-01','value-02','value-03','value-04','value-05','value-06','value-07','value-08','value-09','value-10','value-11','value-12','value-13');"
> into tSQL
>  replace "value-01" with item 1 of l in tSQL
>  replace "value-02" with item 2 of l in tSQL
>  replace "value-03" with item 3 of l in tSQL
>  .
>  .
>  replace "value-13" with item 13 of l in tSQL
>  revExecuteSQL tDatabaseID, tSQL
>  end repeat
> revcloseDatabase tDatabaseID
>
> I tried this with livecode server 3.5.0 (original on-rev) and livecode
> server 5.0.2.
>
> Any ideas anyone?
>
> Regards,
>
> Matthias
>
>
>
>
>
>
>
>
> _______________________________________________
> 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
>