LiveCode and SQLite performace
bonnmike at gmail.com
Wed Jul 22 17:12:24 EDT 2015
If its on an external usb drive, part of the wait is most likely the drive
wake up time. To test, move the file to a local drive and try it from
there, OR, open the drive in finder first and poke around a little to make
sure the drive is live, then try to open the database and see if it solves
On Wed, Jul 22, 2015 at 2:46 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
> 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
>>> 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
>>> 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
>>> database with sqlitebrowser.
>>> Any advise or incites in how sqlite actually works would be appreciated.
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
More information about the Use-livecode