Make a wiki page.
On Thu, 10 Feb 2005 11:18:39 +1100, Chris Schirlinger <[EMAIL PROTECTED]> wrote: > 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 :) > > -- Cory Nelson http://www.int64.org
