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

[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
>