LiveCode and SQLite performace

Mark Waddingham mark at livecode.com
Fri Jul 24 12:46:50 EDT 2015 

Hi Mike,

The only reference to slow opening of SQLite dbs on the Internet was to do with file permission issues on windows so if you are seeing this on Mac it very much sounds like a hard disk issue.

One of my colleagues had an issue recently with the hard drive in a 2011 iMac which didn't show up as a problem in any of the macs internal diagnostics. We replaced the drive and all was well again.

It would periodically get exceptionally slow when opening various files.

I'd get your hdd checked out asap.

Mark.

Sent from my iPhone

> On 23 Jul 2015, at 22:08, 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