LiveCode and SQLite performace

Michael Doub mikedoub at gmail.com
Wed Jul 22 22:46:41 CEST 2015


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
>




More information about the use-livecode mailing list