SQL lite adding records

[JB]

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