LiveCode and SQLite performace

JB sundown at pacifier.com
Thu Jul 23 18:37:23 EDT 2015 

If I remember correctly Bob Sneidar said that a
you need at least 1/2 of your hard drive as free
space to run efficiently.  So if you have a drive
with 500 GB you need 250 GB or more free
space on the drive.  Anything below that and
it normal operations like opening files will be
slower.  I have used more space than 1/2 and
the more I use the slower it gets.  Sometimes
you can speed things up a little by relaunching
the Finder.  That can be done using the Force
Quit option.  If it speeds things up it will only be
a temporary fix.

John Balgenorth




On Jul 23, 2015, at 2:15 PM, Peter Haworth <pete at lcsql.com> wrote:

> Thanks for letting us know Mike.
> 
> You might try running Diskutil to check out your hard drive.  Or maybe the
> disk is getting full enough that there's not enough paging space?
> 
> On Thu, Jul 23, 2015 at 2:12 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
>> 
> _______________________________________________
> 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