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_150...@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_150...@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
