I just updated the database and it now has 608436 records. Sorry for
the typo. it was 604000.
How long to open - 216 seconds.
I timed put revDataFromQuery(,,db_id,"select * from mydatabase") into
tResult
it was 26 seconds.
216 to open and 26 to copy all of the data into a variable. This seems
odd to me.
The database is on the main internal drive (same as OS) in folder with
the stack that is accessing the database.
The result of the integrity check is "ok"
On 7/22/15 5:11 PM, Peter Haworth wrote:
Hi Michael,
Out of interest, when you say it takes a long time to open the database,
how long do you mean?
Also, where is the database located? On your Mac's hard drive, external
drive, on a network?
I'm a bit confused as to the number of records. Your original email said
600,000+ records, but you mentioned that the ID field (which is defined as
unique) has values from 1 to 60400. Maybe a type somewhere?
You'll see auto indexes for any columns that are defined as UNIQUE, that's
how sqlite enforces that constraint.
Don't worry about indexes for now. They on;y help if you are having
problems with how long it takes to execute your select commands and this
problem is occurring long before then.
PRAGMAs are just another type of sqlite statement. After opening your
database, do this:
put revDataFromQuery(,,gDBID,"PRAGMA integrity_check)) into tResult
put tResult
gDBID is just the variable with your connection ID in it so replace it with
your variable name.
The message box will open and you'll see the output from the PRAGMA
command. It it begins with "revdberr",Livecode detected an error with the
statement for some reason. If the PRAGMA does not find any error it
returns OK. Anything else, there's corruption in your database.
Let me know the result of the integrity check and we'll go from there.
On Wed, Jul 22, 2015 at 1:50 PM Michael Doub <miked...@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 <miked...@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@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@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@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@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@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
