Have a large (about half a Gb) SQLite db file, made with version 3.6.1.
I make this file on one PC (Windows XP) put the file on a USB stick, take it
home, copy it to the home PC (Win XP) local drive and then from there copy
the file to a Windows mobile (WM6) device, a Samsung Omnia.
On that device I access the db file with an application written with
Basic4PPC, which uses the .Net framework. SQLite version used by Basic4PPC
is 3.3.12.
Now the thing I don't understand.
I run the following query (on the mobile device):
SELECT EXISTS(
SELECT PATIENT_ID
FROM TABLE1
WHERE PATIENT_ID = 123)
There is an index on field PATIENT_ID.
Takes query takes quite long, say about 1 second.
Now I do the following:
On the home PC I access the SQLite db file, again with 3.6.1 and I drop a
table in that database. This table is only small and completely irrelevant
In the above query. After doing that I copy that db file to the mobile
device, so overwriting the old file. Now when I run that same query it is a
multitude faster, say maybe 20 milli-seconds. I can achieve the same by
dropping an index, again in a small table, completely unrelated to the above
query and I see the same speed gain. I have compared the 2 different
database files, so stats, all the different pragma's etc. and I can't see
the difference. I have also compared the SQLite query plans and they are the
same (using the index) for both files. So what possibly could explain the
difference in speed?
I know it is a bit of a long-winded question, but maybe somebody has some
idea what is going on here and if so, very grateful for that as I can't see
it.
RBS
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
