LiveCode and SQLite performace

Peter Haworth pete at lcsql.com
Wed Jul 22 12:15:35 EDT 2015


Hi Michael,
Haven't come across that before.  600k records is a large number but I've
seen dbs with millions of records in them so it's not unusual.  In any
case, almost every performance problem I've seen revolves around either
selecting or changing data not simply opening the database.

Try executing a PRAGMA integrity_check command after you open the
database.  It checks out the physical integrity of the database and returns
information about any errors it finds.

If that doesn't reveal anything, there are a number of PRAGMA statements
that affect cache size, buffer sizes, etc which might help but I'm not very
familiar with them.  If needed, I can post your problem on the sqlite
mailing list and see if anyone can help.

What platform is this running on?

Pete


On Tue, Jul 21, 2015 at 4:59 PM 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
>



More information about the use-livecode mailing list