LiveCode and SQLite performace

[Mark Wieder]

On 07/21/2015 04:55 PM, Michael Doub 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.

Have you created an index? That could significantly speed things up.
If your queries are always using the tag, unique and local fields then 
you might consider creating a compound index as well.

How big is the database file?
Are the queries slow or just the initial opening?

This might help:  https://www.sqlite.org/optoverview.html

-- 
  Mark Wieder
  ahsoftware at gmail.com