Joe Wilson wrote:
You've probably read this. It's useful information for any performance minded developer using SQLite: http://developer.mozilla.org/en/docs/Storage:Performance
I read it, and I'm now weeping! Below I've reproduced the paragraphs that cause me some consternation: -------------------------------------------------------------------------- "Sqlite has a cache of database pages in memory. It keeps pages associated with the current transaction so it can roll them back, and it also keeps recently used ones so it can run faster. "By default, it only keeps the pages in memory during a transaction (if you don't explicitly open a transaction, one will be opened for you enclosing each individual statement). At the end of a transaction, the cache is flushed. If you subsequently begin a new transaction, the pages you need will be re-read from disk. "[Enabling] the sqlite shared-cache mode ... makes multiple connections to the same database share the same cache. Because the cache is not threadsafe, this unfortunately means that you can not have different connections from different threads accessing the same database. However, the shared cache allows us to keep it live between transactions, instead of clearing it after each transaction as sqlite does by default. "If your application uses many small transactons, you can get a significant performance improvement by keeping the cache live between transactions. This is done by using an extra "dummy" connection to the same database. The dummy connection keeps a perpetually open transaction which locks the cache in memory. Since the cache is shared with the main connection, the cache never expires. "The dummy transaction must be one that locks a page in memory. A simple BEGIN TRANSACTION statement doesn't do this because sqlite does the locking lazily. Therefore, you must have a statement that modifies data. It might be tempting to run a statement on the sqlite_master which contains the information on the tables and indices in the database. However, if your application is initializing the database for the first time, this table will be empty and the cache won't be locked. nsNavHistory::StartDummyState- ment creates a dummy table with a single element in it for this purpose. "It is important to note that when a statement is open, the database schema cannot be modified. This means that when the dummy transaction is running, you cannot create or modify any tables or indices, or vacuum the database. You will have to stop the dummy transaction, do the schema-modifying operation, and restart it." -------------------------------------------------------------------------- If the above is correct, it is not enough for the server thread to open connections on behalf of client threads: The server must also open its own connection, do a BEGIN TRANSACTION, create a dummy table containing a single element, and then keep this dummy transaction open by *not* doing a COMMIT. Is this really true? I don't remember seeing this sort of dummy transaction code in the src/test_server.c file. Regards, - Richard
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
