SQL lite adding records

[Erik Beugelaar]

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 <bonnmike at 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 <sundown at 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 <pete at 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 <sundown at 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 <pete at 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 <sundown at 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