I am not surprised. A lot of developers do not know the power and tricks of presenting and performing data via SQL language.
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 maili _______________________________________________ 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
