System.Data.SQLite

As part of our application we use a SQLite database to store information about messages that are sent through Exchange.  I am trying to track down some odd performance degradation.  As the number of records in a particular table of the database gets into the thousands (and the database size approaches 1GB) I am noticing that the first select query performed on the table takes a long time.  Subsequent queries are virtually instantaneous.  What is most curious is that if the application is stopped and restarted, the query is still fast!  It's only if substantial changes are made to the data or the database completely overwritten that the initial behaviour is observed.

 The structure of the table is as follows: -

 CREATE TABLE SuspiciousMail (
  ID                             integer PRIMARY KEY NOT NULL,
  AddedWhen                      datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  SentDate                       datetime NOT NULL,
  Subject                        ntext,
  MailDirectionID                int NOT NULL,
  Size                           bigint NOT NULL,
  FirstAttachmentID              long,
  RecipientList                  ntext NOT NULL,
  BodyText                       ntext,
  EvidenceData_OriginalFilename  nvarchar(260),
  EvidenceData_FileSize          real,
  EvidenceData_AttachmentCount   long NOT NULL DEFAULT 0,
  EvidenceData_From              nvarchar(500),
  EvidenceData_Size              real)

The query being run is

Select Count(ID)

From SuspiciousMail;

The query is being run against the primary key and should therefore be able to use the associated index.

On first run, the query can take anything up to 30 seconds to complete.  Subsequent runs are fractions of a second (e.g. 0.09)

 My gut instinct is that some kind of OS-level caching is going on (I'm running this on Vista Business edition 32 bit).  What (if anything) can I do to improve this?

I'm using version 1.0.65 of the NET provider component for my application (SQLite version 3.6.16).  However I can observe the same behaviour using tools that talk directly to the database.

 Any help would be gratefully received.

 Nick Hall

I've encountered the same behavior and I suspect it is the effects of disk caching. On the first access you're taking the hit of waiting for the disk reads to complete. Subsquent reads will occur in much faster memory and this behavior will persist until Windows decides to flush that infomation from cache. I couldn't find any sure fire way to clear the cache on demand, so I do all my performance benchmarks on a system I don't mind rebooting between tests.  

There is another odd performance characteristic that I have encountered and have no explanation for. My database is very large and weighs in at 57 gigabytes. I noticed my initial SELECT query was taking about 10 minutes and I originally attributed that to the size of the database; however, after running the VACCUM command the same queries occur in under a minute. The VACCUM cleared out a minimal amount of space and there is nothing I see that is obviously different when I browse through the data. You may want to try this and see if it improves your performance. I'd really like to know what what factors would cause the database performance to degrade so significantly over time.

 -Justin