I've looked some more at this (unfortunately using 2.8.16 although
the problem is also exhibited by 3.3.4) and the problem seems to be
in sqlitepager_commit and its call to pager_get_all_dirty_pages which
returns every page of the database as dirty and then proceeds to
write every page to disk by calling pager_write_pagelist. Only one
record has been changed, and of course as the database gets larger
the more writing it does. I've been building the Windows sqlite
source with CodeWarrior 9.6 on the Mac and targeting the Windows
platform. I'm wondering if there is some compile define I should be
setting but am unaware of.
Milt
On Apr 04, 2006, at 17:02, Milton Sagen wrote:
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
