Thanks, Mike! I have no doubt what I will be using now. Good info & thanks.
John Balgenorth On Mar 25, 2015, at 1:16 PM, Mike Bonner <bonnm...@gmail.com> wrote: > Part of the slowdown when inserting a large number of rows one at a time is > the opening and closing of the transaction file. If you want to see this > in action, you can set a loop up to do repeated single row inserts, then > watch in your file browser next to the db file as the transaction file > appears, disappears, appears, disappears, over and over and over. There is > a bunch of overhead with creating and removing this file for each insert in > sqlite. By using begin/commit, the transaction file is only created once, > and the speedup is tremendous. > > On Wed, Mar 25, 2015 at 12:53 PM, JB <sund...@pacifier.com> wrote: > >> Okay, thanks! I have been looking >> a SQLYoga and see it has search, >> sort and find. >> >> It seems to use arrays but with using >> large amounts of data in a data grid >> you need to use a cursor. >> >> SQLYoga does provide the ability to >> use cursors but since it is using the >> arrays is that using a lot of memory? >> >> John Balgenorth >> >> >> On Mar 25, 2015, at 10:03 AM, Peter Haworth <p...@lcsql.com> wrote: >> >>> Whoops! Thanks for letting me know about the 404 error - will go to fix >> it >>> right now. >>> >>> SQLIteAdmin does have query/sort features but, just to be clear, it is a >>> standalone program so you can't see the code. >>> >>> Pete >>> >>> On Tue, Mar 24, 2015 at 7:14 PM JB <sund...@pacifier.com> wrote: >>> >>>> Hi Peter, >>>> >>>> Thank you so much for the detailed info! >>>> >>>> Each value has single quotes now but it >>>> would be nice to do it without quotes as >>>> adding them takes more time. >>>> >>>> I will definitely use th BEGIN statement >>>> and thank you for explaining it. I tried to >>>> use revCommitDatabase after the repeat >>>> without the BEGIN and it was missing a >>>> file. Your way will be a lot better and I >>>> will include the error checking too. >>>> >>>> I really do not know that much about using >>>> a database so if things look weird it is due >>>> to me not knowing what I am doing. >>>> >>>> I tried to go to your site and look at the stack >>>> you have for sql database administration but >>>> I got a 404 error. Does it include code to sort >>>> and find with SQL? If so I am interested. >>>> >>>> Thanks again! >>>> >>>> John Balgenorth >>>> >>>> >>>> On Mar 24, 2015, at 6:52 PM, Peter Haworth <p...@lcsql.com> wrote: >>>> >>>>> Hi John, >>>>> A few ideas for you. >>>>> >>>>> You should issue a BEGIN statement right before your repeat statement >> and >>>>> move your revCommitDatabase to right after the end repeat. Better >>>>> performance and guarantees db integrity. >>>>> >>>>> For some reason I don't understand, LC does not have a revxxx statement >>>> to >>>>> issue a BEGIN so you will need to use revExecuteSQL gConID,"BEGIN" >> right >>>>> before the repeat statement. >>>>> >>>>> Within the repeat, don't forget to check if revExecuteSQL resulted in >> any >>>>> errors by checking "the result" right after it - it will be a numeric >>>> value >>>>> if all worked OK, otherwise an error message. If an error occurs, >> issue >>>> a >>>>> revRollBackDatabase command and exit the repeat. That will return your >>>>> database to a consistent state before any of your INSERTs happened. >>>>> >>>>> I'm a little confused by your INSERT statement. You name 4 columns in >>>> the >>>>> table but only supply 2 values, maybe tLine has comma separated values >> in >>>>> it? If so, you will need to enclose each value in single quotes if they >>>> are >>>>> not numeric. >>>>> >>>>> Also, if recID is the primary key, no need to include it in the list of >>>>> columns to be updated or supply a value for it, SQL will take care of >>>> that >>>>> for you. >>>>> >>>>> Hope that helps, >>>>> >>>>> On Tue, Mar 24, 2015 at 5:04 PM JB <sund...@pacifier.com> wrote: >>>>> >>>>>> I am using MySql on a local host and >>>>>> creating a database then adding records. >>>>>> After creating the table I was using the >>>>>> handler below to add records. I was not >>>>>> using the revCommitDatabase gConID >>>>>> statement after each record and some >>>>>> times my database would be missing >>>>>> one or two records. After including the >>>>>> revCommitDatabase gConID statement >>>>>> it appears to be including them all. Is >>>>>> this the proper and fastest way to add >>>>>> records in a sql lite database or should >>>>>> I be using something else like a query >>>>>> to update instead? >>>>>> >>>>>> on fAddFiles >>>>>> set the itemDelimiter to tab >>>>>> put fld id 5246 of cd id 5187 into tList >>>>>> repeat for each line tLine in tList >>>>>> put "INSERT INTO tFiles(recID,field2, field3, field4) >> VALUES(null," >>>>>> & tLine & ")" into sqTable >>>>>> revExecuteSQL gConID,sqTable >>>>>> revCommitDatabase gConID >>>>>> end repeat >>>>>> fRefresh >>>>>> gConIDClose >>>>>> end fAddFiles >>>>>> >>>>>> Any information or ideas are helpful. >>>>>> >>>>>> John Balgenorth >>>>>> _______________________________________________ >>>>>> 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 >> > _______________________________________________ > 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
