After a machine restart the first time I try to change a field in a
record, on Windows, it takes an inordinately long time for the sqlite
code to return, the amount of time is dependent on the size of the
database but even for a 20 Meg one with about 4000 records, the time
is in the order of a minute. Once the change is made I can quite the
program and relaunch it, open the database, and make another change
exactly like the first to another record and the sqlite code returns
almost immediately. On the Mac OS X, I don't see this delay.
I see this with SQLite 2.8.16 as well as 3.3.4.
Here's the schema for the database:
CREATE TABLE MyTable (RID Integer Primary Key, Filename Text,
PathType Integer, Path Text, Category Text null, Favorite Boolean
null, Missing Boolean null, Thumbnail Blob null);
CREATE UNIQUE INDEX MyTable_RID on MyTable (RID);
CREATE INDEX MyTable_Filename on MyTable (Filename);
a record is added with
INSERT into MyTable (RID, Filename, PathType, Path, Category,
Thumbnail) (10000, 'afile.jpg', 1, 'path', 'data');
where
path = dos path (windows path - whatever you want to call it - it
starts with c:\)
data = hex encoded data of a raw bit map - (this originally used
2.8.16 and I could see any other way to get raw binary data into the
database)
the update is simply this:
UPDATE MyTable set Favorite = 1 where RID = 10000;
In fact I find that I get a delay anytime I change the database
following a reboot of the machine, i.e. deleting a record, inserting
a record, updating a record.
The routine that seems to get take the time is sqlite3VdbeExec.
Anybody have any idea what might be going on here or how to fix it?
Why does it only happen after a reboot of the machine? Similarly, and
this I see on both the Mac and Windows if on launch I do the following:
select rid from MyTable order by lower(filename);
on a database with approximatley 4000 records it will take about 14
seconds, after a reboot, to complete. Quitting the program and
relaunching on the Mac the select completes in 0.04 seconds.
Milt
