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

[Matthias Rebbe]

Peter,

thanks for your explanation. I will definitely try that out. But i have to meet a deadline
and there is still one problem left i have to solve. But nothing db related.

Thanks again,

Matthias




Am 02.06.2012 um 23:14 schrieb Peter Haworth:

> Hi Matthias,
> Glad that worked.  With the Begin/Commit, the changes don't get flushed to
> disk until the COMMIT; without the BEGIN/COMMIT, each individual INSERT
> gets flushed to disk, plus other sqlite overhead.
> 
> The INSERTs are probably fine.  I was going to suggest that you use the
> parameter replacement formm to protect against SQL injection and also deal
> with escaping single quotes so you don't have to do it yourself.
> 
> You can find the details in the dictionary but basically, your call to
> revExecuteSQL would look like:
> 
> revExecuteSQL tdatabaseID, "INSERT INTO produkte VALUES (:1,:2,:3,:4,:5
> .... :13)","tarray"
> 
> Essentially, sqlite does the replacement for you by replacing ":1" with the
> value of tarray[1], etc.
> 
> 
> Your code would look like this:
> 
> repeat for each line l in tlist
>   put empty into tarray
>   repeat with x=1 to 13
>      put item x of l into tarray[x]
>   end repeat
>   revExecuteSQL tdatabaseID, "INSERT INTO produkte VALUES
> (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)","tarray"
> end repeat
> 
> Just be sure to put quotes around "tarray" at the end of the revExecuteSQL
> statement.
> 
> I doubt you'll see any further performance improvements but it shortens the
> amount of code and, as mentioned, takes care of any esacping that needs to
> be done and SQL injection issues.
> 
> Good luck,
> 
> Pete
> lcSQL Software <http://www.lcsql.com>
> 
> 
> 
> On Sat, Jun 2, 2012 at 1:53 PM, Matthias Rebbe <
> matthias_livecode_150811 at m-r-d.de> wrote:
> 
>> 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
>> 
>> 
>> _______________________________________________
>> 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