Page History
After spending a lot of time on getting SQLite to perform at speed in a large database, small user system, I've come across a couple of things that may help others in speeding up their own programs
As a side note, these large databases were 3 million rows plus and SQLite still dealt with this much data gracefully
I have also included a bonus "dumb thing to do" if you read far enough (or cheat and just skip to the last item). It's dumb, and it happened to me, so I felt the need to give fair waring to everyone!
These mostly relate to Windows and Delphi environments but may help others, and refers to SQLite 3.1.0:
One: Talk to the SQLite exported functions directly
I recommend you try and use the SQLite functions directly, ExecSQLing stuff down to the DB is better than trying to use some faked Locate or Seek function.
Even if you have to change the way you code, I recommend it. From what I experienced, the exported functions from the DLL were simple to use and did everything you wanted, though perhaps slightly differently from the Delphi DBE way some people may be used to
I found a simple SqliteWrappers worked better than trying to use a bunch of components pretending to be the BDE, but of course, a lot depends on what you want to do and how confident you are using exported DLL functions
SQL Statements like INSERT OR REPLACE INTO can replace entire wads of old Locate IF Found Update ELSE Insert code, keeping the code neater AND speeding it up
Two: Indexes and DB structure are important!
Sure, a fairly generic DB rule One, but this is an SQL DB, its VITAL you add the indexes you need, and even MORE important you DON'T add the indexes you don't need. Plan your DB before hand with an eye that everything has a function, if you don't use it, don't have it.
Having two indexes on the same key (in composite and also indexed alone for example) can slow down insertion of data while giving you no advantage when retrieving it since SQLite will ignore one of them. It will also increase the size of your database at the very least, even if speed isn't an issue for you
There are other Wiki pages like PerformanceTuning that explains this in more detail.
Three: Page Size can make a difference
The default cluster size for a Windows NTFS system seems to be 4096 bytes. Setting the SQLite database page size to the same size will speed up your database on systems where the cluster size is the same
(Note, Linux cluster I believe to 1024 which is the default for new SQLite databases)
Easiest way to tell your cluster size is to defragment your drive and analyse. It tells you in there
To set the SQLite page size, create a new EMPTY database and do a
PRAGMA page_size=4096;
Now create your tables immediately (if you close down the SQLite command line program and reopen the DB, the page size is reset to 1024). The page size must be set before the first table is created.
Once that tables made, you can't change the size
Typing:
PRAGMA page_size;
will tell you what it is currently set at
Four: Clustered Indexes
SQLite doesn't support clustered indexes (simply, indexes that force the data in the database to be physically laid down in the SAME order as the index needs it to be in.)
This means that if your index is sequential INTEGER, the records are physically laid out in the database in that INTEGERs order, 1 then 2 then 3.
You can't make a Clustered index, but you CAN sort your data in order so that any historical data is ordered nicely. Of course, as the database matures, you lose that, but it helps
Someone else posted this, and it is a nice example to use, so I will. If you have a table WIBBLE whose field KEY you want to access a lot, it would be nice if everything was in order. Using the command line tool, you can create a fake cluster by doing the following:
create table wibble2 as select * from wibble;
delete from wibble;
insert into wibble select * from wibble2 order by key;
drop table wibble2;
Five: Ok, as a reward for reading this far, here is the dumb thing.
Be VERY, VERY careful what you name your database, especially the extension
For example, if you give all your databases the extension .sdb (SQLite Database, nice name hey?) you discover that the SDB extension is already associated with APPFIX PACKAGES.
Now, here is the cute part, APPFIX is an executable/package that Windows XP recognizes, and it will, (emphasis mine) ADD THE DATABASE TO THE SYSTEM RESTORE FUNCTIONALITY
This means, stay with me here, every time you write ANYTHING to the database, the Windows XP system thinks a bloody executable has changed and copies your ENTIRE 800 meg database to the system restore directory....
I recommend something like DB or DAT.
Have fun!
Chris Schirlinger
chrisZEROSPAM@ZEROSPAMtimezulu.com.au (remove ZEROSPAM to email)