Re: [LiveCodeServer / on-Rev] SqlLite - Insert…. very slow
Matthias Rebbe
matthias_livecode_150811 at m-r-d.de
Sat Jun 2 16:53:10 EDT 2012
Peter,
thank you very much. That is more than an improvement. It takes now just a second or so.
You mentioned the form of my insert statement. Is there something wrong with it?
Do you mean the fact that i replace the placeholders value-xx?
Regards,
Matthias
Am 02.06.2012 um 22:10 schrieb 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
>>
> _______________________________________________
> 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
More information about the use-livecode
mailing list