Why the speed difference in these 2 SQLite files?

[RB Smissaert]

This is a tricky one, and not sure anybody has any idea about this, but let me try to explain.
I have a large (about 0.5 Gb) SQLite db file, which I make on a work PC (Win XP) with VBA/VB6. The SQLite version here is 3.6.1. I then copy this file to a USB stick and take it home where I copy this file to the home PC, Win XP as well. I then copy the file to the mobile device, a Samsung Omnia. It goes the internal storage, called My Storage. This is not a card.
Now I run a number of queries like this:

SELECT EXISTS(
SELECT PATIENT_ID FROM TABLE_X
WHERE PATIENT_ID = @value1)

PATIENT_ID is an INTEGER PRIMARY KEY and these tables are not that large, but this query can be slow, say about one second.

Now the strange thing.
I now make a trivial change (set Read-Only to True and then back to False)
to the db file on the home PC and copy that file to the device again, so over-writing the old SQLite file.
I then run the same queries again and they are a lot faster, say 20 milli-seconds. Apart from that trivial change the fiiles are otherwise exactly the same and the SQLite query plans for both queries are the same.

It is not a cache effect as I can reverse it from fast to slow by copying the
first file (without the trivial change) to the device again.
All this is not entirely consistent, so sometimes the trivial change may not make the queries go faster.
Doing the same change (Read-Only True and then False) on the device unfortunately doesn't make it go fast.
So, what possibly could be going on here?
The slow queries are a problem, so I need to figure out what is doing this and how to prevent/solve it.

Any ideas/suggestions?

RBS

 

[Cableguy]

Not quite how to solve it, but you can replicate the setting the "readOnly" prop with one of Agraham's dll's....
There fore you can do that everitime the app is started....
Also, you should do some "vacuum", an sql command that gets rid of all the empty spaces left in the DB...just before closing it at least...This might reduce the DB in some space, specially if you do a tot of replace/Add/delete oeprations, as they tend to leave empty spaces...

 

[RB Smissaert]

Doing the Read-Only True then False change is not a problem, I can do that on the desktop device. In fact it doesn't work (doesn't make it go fast) when done on the device. Other changes (completely irrelevant to the mentioned queries) such as a minor update can bring on the speed improvement as well, but it has to be done on the desktop. As all this is not consistent I really would like to understand what is going on here.
I know the SQLite vacuum method, but that is no option as it will just take too long as this is a 0.5 Gb SQLite file.

RBS

 

[Cableguy]

How are you transfering the db file?
copying it directly to an SD using a card reader or using activesync data transfer?
That may be the issue...

 

[RB Smissaert]

I make the device an external USB drive (Settings, Connections, USB connection) and then copy the file. So, ActiveSync is bypassed. Unfortunately, for some reason I can't do this on the work PC (not sure why) and that is why I copy to the USB stick first then copy to the home PC and then copy to the device.

RBS

 

[Erel]

It sounds like some optimization done by SQLite.
There are Pragama statements you can use to retrieive some information about the indexes and other related stuff. Maybe it will shed some light on this issue.

 

[RB Smissaert]

I have compared all the pragma related parameters in the 2 files and they are all the same. The only thing I can think of (well, suggested by the author of my VB SQLite wrapper, Olaf Schmidt) is that somehow the slow file has a tiny, non-fatal corruption and the trivial file write (toggle Read-only or other trivial writes) brings it back into shape. This is still puzzling me and not sure what to try next. One thing I could do is try to reproduce this problem with much smaller SQLite files and I could then post these.

RBS