I've been posting a lot on speed and SQLite, just thought I'd dump
the results and findings into one message including a nice example on
how dumb you can get in the hopes no one else falls this low :)
After several weeks of playing with SQlite3 we have finally gotton
what we want out of a DB. For single user apps (I can't talk much
about multi-user, that wasn't what we were doing) SQLite is
fantastic, fast, easy to use and has great functionality. (hell, it's
even fun, I wuuuuv INSERT OR REPLACE INTO)
Here's the various things we discovered to speed up the system the
most. This relates *mostly* to Windows and Delphi enviroments but may
help others:
1) Talk to the SQLite functions directly if you can, 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 wrapper class worked better than trying to use a
bunch of componants 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
2) Indexes and DB structure are important. Fairly generic DB rule one
I suppose, but this is an SQL DB, its VITAL you add the indexs you
need, and even MORE important you *DON'T* add the indexs you don't
need.
Plan your DB before hand with n eye that everything has a function,
if you don't use it, don't have it. There is more about this on the
SQLite docs
3) 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
analyze. 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 immedeatly (if you close down the SQLite
commandline 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
4) SQLite doesn't support clustered indexes (simply, indexes that
force the data in the database to be physically layed 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 layed 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;
5) 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 people and thanks for all the help :)
