Mailing Lists: Apple Mailing Lists
Image of Mac OS face in stamp
 
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: SQLite optimization


On Aug 29, 2006, at 1:16 AM, Ruslan Zasukhin wrote:

On 8/28/06 1:48 PM, "Mark Gilbert" <email@hidden> wrote:

Hi Mark,

Hi There.

My app accesses the same SQlite database from several threads, and in
order to accommodate this I currently open and close the database on
each access, and the various threads will wait when the database is
busy on another thread.

This all works OK, but I am finding performance issues, which I think
may be related to the opening and closing.

My idea is to limit access to the database to a single intermediate
thread, with each other thread passing their requests through the
'owner' thread for the SQLite database.  My hope is that this thread
can leave the database permanently open with a large cache size and
consequently speed things up.

In fact SQL Lite self do open/close of db file for each transaction.
This was idea of its author. Many solutions in SQL Lite design rely on this
idea, and this is why it is underlined that "SQLLite is single user engine".

You don't say whether you are querying the database or inserting data. SQLite does not open the db file for each SQL statement, but when inserting data it creates a separate journal file for each transaction, so there is a fair amount of overhead per insert transaction. As another reply stated, you can keep several open connections (and they can be from separate threads) to the db and let the SQLite take care of synchronization.

Would anyone with experience in SQLite performance care to comment if
this is a good strategy to improve performance ?

I suggest you try keeping your connections open and batching inserts in a transaction, if you are doing a lot of inserts.

----
Aaron Burghardt
email@hidden

_______________________________________________
Do not post admin requests to the list. They will be ignored.
Darwin-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/darwin-dev/email@hidden

This email sent to email@hidden
References: 
 >Re: SQLite optimization (From: Ruslan Zasukhin <email@hidden>)



Visit the Apple Store online or at retail locations.
1-800-MY-APPLE

Contact Apple | Terms of Use | Privacy Policy

Copyright © 2007 Apple Inc. All rights reserved.