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, it was my fault, so I felt the need to give fair warning to everyone!
These mostly relate to Windows and Delphi environments but may help others, and refers to SQLite 3.1.0:
Zero: (optional) Set Windows Caching
If you have the RAM to spare and you are using very large database, then this optional Windows setting will dramatically improve SQLite performance using temporary tables (sorting, group by, subselects, etc):
Setting Windows Temporary File Cache http://support.microsoft.com/kb/895932 http://www.techspot.com/tweaks/memory-winxp/
One: Talk to the SQLite directly
I recommend you try and use the SQLite directly, ExecSQLing stuff down to the DB is often better than trying to use some faked Locate or Seek function. (Since SQLite doesn't have these functions, component programmers often need to produce complex and CPU/HDD expensive code to emulate the BDE functionality)
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 personally found that a simple SqliteWrappers worked well for us, allowing us to send SQL to the DB and get back exactly what we wanted quickly. Of course, a lot depends on what you want to do and how confident you are using exported DLL functions
If you want to Append, insert or update new data into a database, SQL Statements like:
DB.ExecSQL('INSERT OR REPLACE INTO tableName (Field1, Field2, Field 3) ' +
'VALUES (Value1, Value2, Value3)');
can replace entire wads of old code like this:
IF Locate(Field1, Value1) THEN
UpdateTableFunction() // ...Lots of code here
ELSE
InsertIntoTableFunction(); // ...even more code HERE
Keeping the code neater AND speeding it up. Remember to wrap Begin Transactions and End Transactions around the update SQL. We run thousands of SQL INSERTS in one transaction to great effect
This is not to say that using componants is bad, and using them can significently speed up your development time. But please do have a look at accessing SQlite through SQL (-> ExecSQL) even if you are using componants. A fine tuned SQL statement that is specific to your applications needs could well be faster than a generic solution.
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 be 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).
You only need to do PRAGMA page_size=4096 once, before your first CREATE TABLE statement. Once the database exists, the page size is fixed and can never change.
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? I thought so when I choose it anyway...) 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.
The entire list of system restore-monitored filename extensions can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sr/sr/monitored_file_extensions.asp
Have fun!
Chris Schirlinger
chrisZEROSPAM@ZEROSPAMtimezulu.com.au (remove ZEROSPAM to email)
After researching a little and writing a simple test program, it doesn't appear that using one of the mentioned file extensions negatively hurts performance. Either the file system hides the difference, or it may only look at system files. The comment above is from before 2005, so the problem may have been fixed since then.
~Jay