SQLite Optimization FAQ. Jim Lyon (jplyon@attglobal.net) 10-Sep-2003 Compiled from sqlite docs, source, and yahoo postings This document is current for SQLite 2.8.6 1. Introduction 1.1. About this FAQ This FAQ is an incomplete summary of my experiences with speed-optimizing an application that uses the SQLite library. It is released under the terms of the SQLite license :) ** The author disclaims copyright to this material. ** In place of a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. 1.2. IntroductionPRAGMA settingsTransactionsIndexesOptimizing queriesMoving code into SQLUser functionsCallback functionsReducing database file sizeReducing database load timeReducing SQL query parse timeHacking the sourceUsing the btree interfaceMultiple threadsOperating system issues AppendicesTiming considerations 1.3.
SQLite is capable of being extremely fast. 1.4. 2. 2.1 PRAGMA cache_size. Using SQLite for fast database testing – technical gotchas and w. Blisteringly Fast Integration Tests With NHibernate and SQLite | One thing I love about using NHibernate as my O/RM is being able to push the database schema from the domain. This lets me create the database from scratch for each integration test fixture and get it into the required state. Creating the database with NHibernate is quick and simple.
Hell, here is the code to do it: 1.Configuration cfg = container.Resolve<Configuration>(); 2.SchemaExport export = new SchemaExport(cfg); 3.export.Execute(true, true, false, true); Nifty, eh? But tests that hit the database are sloooow, and these quickly become tests that are not ran. Luckily; NHibernate provides a number of RDBMS providers. Enter SQLite, "the most widely deployed SQL database engine in the world". A quick modification to to the NHibernate and the tests were up and running as fast as a pig to...
Setting everything up was quick and painless. First: grab the required assemblies from and add a reference. 01.facility NHibernateFacility: 02. configuration: 03. 04. factory: Here comes dotConnect for SQLite.