LiveCode and SQLite performace

[Mike Bonner]

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
the issue.

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
> 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
>