LiveCode and SQLite performace

Mike Bonner bonnmike at gmail.com
Thu Jul 23 17:17:55 EDT 2015 

Sounds like something is pegging a core to max.  Its been a while but I've
seen instances where the mdnsresponder horks, or the spotlight re-index
takes a left turn. And as  Pete just said, could be a failing drive or lack
of paging.

On Thu, Jul 23, 2015 at 3:08 PM, Michael Doub <mikedoub at gmail.com> wrote:

> Hey guys,
>
> I think that I have a system issue of some type.   We had a nasty
> thunderstorm last night and we lost power.  Obviously my Mac was
> re-booted.  When I ran my timing tests this morning.   Open was 3 seconds
> and loading all the data in to a variable was 1.   Much better.
>
> I just got back from a nice bike ride and tried it again.. some 5 hr later
> with the mac running the whole time.   Open was 93 seconds and loading the
> data was 23.
>
> This tells me that it has nothing to do with livecode or the database, but
> something is going on with my system that is causing a dramatic slowdowns.
>
> -= Mike
>
>
>
>
> On 7/23/15 1:46 PM, Peter Haworth wrote:
>
>> Hi Michael,
>> Those are ridiculously long times!  I can't think of anything obvious that
>> would cause them.  Mark's question about whether the automatic indexes are
>> being recreated is a possibility but I'm pretty sure that doesn't happen.
>>
>> I couldn't tell from your original post if your table has a primary
>> keydefined, looks like the ID column might be it?  Reason I ask is if
>> would
>> be worth trying "SELECT <primarykeycolumn> FROM mytable" since selecting
>> all primary key columns is the absolute fastest select statement on a
>> table
>> so I'd be interested to see how long it takes.  If you don't have a
>> primary
>> key defined, use "rowid" instead.
>>
>> That doesn't help with the open time but it might shed some light and
>> what's going on.
>>
>> If you would be willing to share the database with me offline, I'd be
>> happy
>> to see if I can spot anything.  I'd also be happy to post the problem on
>> the sqlite developers list but in order to do that, I would need the exact
>> CREATE statement used to create the table plus any other tables in the
>> database.  Your sqlitebrowser tool should provide that somewhere.
>>
>> Pete
>>
>>
>>
>> On Wed, Jul 22, 2015 at 4:33 PM Michael Doub<mikedoub at gmail.com>  wrote:
>>
>>  I just updated the database and it now has 608436 records.  Sorry for
>>> the typo. it was 604000.
>>>
>>> How long to open - 216 seconds.
>>>
>>> I timed put revDataFromQuery(,,db_id,"select * from mydatabase") into
>>> tResult
>>> it was 26 seconds.
>>>
>>> 216 to open and 26 to copy all of the data into a variable.  This seems
>>> odd to me.
>>>
>>> The database is on the main internal drive (same as OS) in folder with
>>> the stack that is accessing the database.
>>>
>>> The result of the integrity check is "ok"
>>>
>>>
>>> On 7/22/15 5:11 PM, Peter Haworth wrote:
>>>
>>>> Hi Michael,
>>>> Out of interest, when you say it takes a long time to open the database,
>>>> how long do you mean?
>>>>
>>>> Also, where is the database located?  On your Mac's hard drive, external
>>>> drive, on a network?
>>>>
>>>> I'm a bit confused as to the number of records.  Your original email
>>>> said
>>>> 600,000+ records, but you mentioned that the ID field (which is defined
>>>>
>>> as
>>>
>>>> unique) has values from 1 to 60400.  Maybe a type somewhere?
>>>>
>>>> You'll see auto indexes for any columns that are defined as UNIQUE,
>>>>
>>> that's
>>>
>>>> how sqlite enforces that constraint.
>>>>
>>>> Don't worry about indexes for now.  They on;y help if you are having
>>>> problems with how long it takes to execute your select commands and this
>>>> problem is occurring long before then.
>>>>
>>>> PRAGMAs are just another type of sqlite statement. After opening your
>>>> database, do this:
>>>>
>>>> put revDataFromQuery(,,gDBID,"PRAGMA integrity_check)) into tResult
>>>> put tResult
>>>>
>>>> gDBID is just the variable with your connection ID in it so replace it
>>>>
>>> with
>>>
>>>> your variable name.
>>>>
>>>> The message box will open and you'll see the output from the PRAGMA
>>>> command.  It it begins with "revdberr",Livecode detected an error with
>>>>
>>> the
>>>
>>>> statement for some reason.  If the PRAGMA does not find any error it
>>>> returns OK.  Anything else, there's corruption in your database.
>>>>
>>>> Let me know the result of the integrity check and we'll go from there.
>>>>
>>>>
>>>>
>>>> On Wed, Jul 22, 2015 at 1:50 PM Michael Doub<mikedoub at gmail.com>
>>>> wrote:
>>>>
>>>>  Kay and Mark, the database file size is 250Mb.   The performance issue
>>>>> that surprised me was the open time.  Adds and Queries are as expected.
>>>>>
>>>>> ID's currently range from 1 to 60400
>>>>> URL is a typical URL that ranges from 50 to 130 characters, average is
>>>>> 80 char.
>>>>> tag is currently not being used so empty
>>>>> local is a mac file path:  of the form
>>>>>
>>>> /Volumes/EXTERNAL/XXX/xxxxxxxxx.xxx
>>>
>>>> How would I create and index?   When I look at the database with
>>>>> sqlitebrowser, it looks like an auto index was created.
>>>>> Can you give me instructions as to how I should have created the
>>>>> database with an index?
>>>>>
>>>>> Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4.   My DataBase
>>>>> expertise is very limited.  I created the database with sqlitebrowser,
>>>>> then added the data with livecode.   I don't know anything about PRAMAs
>>>>> or even how to execute them.   If you can provide instructions I will
>>>>> give it a try.
>>>>>
>>>>> -= Mike
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On 7/21/15 11:48 PM, Kay C Lan wrote:
>>>>>
>>>>>> An example of half a dozen records please. Maybe a couple of very
>>>>>>
>>>>> short,
>>>
>>>> a
>>>>>
>>>>>> couple of the longest and a couple of average.
>>>>>>
>>>>>> How big is the db file size - MB not record count?
>>>>>>
>>>>>> On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub<mikedoub at gmail.com>
>>>>>>
>>>>> wrote:
>>>>>
>>>>>> I need some database consulting help.   I have created an SQLite
>>>>>>>
>>>>>> database
>>>>>
>>>>>> that has 4 fields:
>>>>>>> ID, URL, tag, local.   IS is integer, unique, URL is text not null
>>>>>>>
>>>>>> unique
>>>>>
>>>>>> and tag and local are just text.   I have 600,000+ records. I use the
>>>>>>>
>>>>>> ID to
>>>>>
>>>>>> access each record in most cases or I am filtering trying to find
>>>>>>>
>>>>>> records
>>>>>
>>>>>> based on the contents of the tag, URL or local fields.
>>>>>>>
>>>>>>> It seems to take forever to open the database.  This is normal?   I
>>>>>>> am
>>>>>>> primarily using the database to make sure that I do not have
>>>>>>>
>>>>>> non-unique
>>>
>>>> URLs, adding performance and selects seem reasonable.   I am just
>>>>>>>
>>>>>> surprised
>>>>>
>>>>>> about the open performance  Does opening the database load a lot of
>>>>>>> information into memory thus the long delay?   Could I have created
>>>>>>>
>>>>>> the
>>>
>>>> database in someway that is not optimal, thus causing the delay?
>>>>>>>
>>>>>>> This is not a livecode issue as I am seeing the same delay when I
>>>>>>> open
>>>>>>>
>>>>>> the
>>>>>
>>>>>> database with sqlitebrowser.
>>>>>>>
>>>>>>> Any advise or incites in how sqlite actually works would be
>>>>>>>
>>>>>> appreciated.
>>>
>>>> Regards,
>>>>>>>       Mike
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> 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
>>
>>
>
> _______________________________________________
> 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
>

More information about the use-livecode mailing list