Re: [sqlite] sqlite3 performace
I tracked down the problem. I was linking an old version of sqlite3 (3.0.8). That was the old version we used. I apparently doesn't cache between calls. My code now performs IO very similar to the CLI. Each subsequent query once the db is open does not require re-reading the whole db. My next task is to solve keeping the database open for longer. The basic application is an rpc server. So its spins up a thread does some work sends reply and closes the thread. Ideally we want to open the db when we start the server and then close it when we shutdown. And then pass the connection into each thread, so we don't have to keep opening the db. In the past we had lots of issues doing this, hence the open for each query model. Any advice would be appreciated. Phil On Jan 18, 2008 8:46 AM, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote: > > Greetings, > > > > Currently I am using sqlite3 in a multi-process/multi-threaded server > > setting. > > opening a file is very slow. You need to move it out of your loop. > > > I have noticed my program basically reads the whole database every time > the > > query is run. The IO Read bytes increases by 900k for every query we > run. We > > also chew a good chunch of the cpu I have tried keeping the database > > connection open and open/closing for every query. But there was no > change. > > > if I launch the CLI and run the query it runs instantly and monitoring > the > > IO read bytes is see only ~20 bytes of read to execute the query, when > my > > code is using over 900k for every call. I have been looking into the CLI > > source to see what is done differently, but was hoping someone on here > might > > have some insight. > > The operating system will cache files it reads in memory. > If your process reads the file and then you open the CLI > it will still be in memory from before and will not give you > comparable times. > > If your process reads the whole database for every call you didn't > optimize > your sql. You need to create indexes to optimize your query > > -- > The PixAddixImage Collector suite: > http://groups-beta.google.com/group/pixaddix > > SqliteImporter and SqliteReplicator: Command line utilities for Sqlite > http://www.reddawn.net/~jsprenkl/Sqlite<http://www.reddawn.net/%7Ejsprenkl/Sqlite> > > Cthulhu Bucks! > http://www.cthulhubucks.com > > > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Philip Nick E-Mail: [EMAIL PROTECTED] Cell: 352-262-9067 Web: http://www.ruffasdagut.com
Re: [sqlite] sqlite3 performace
Thanks for replying, I have tried moving the Open/Close outside the mutex no change. As for using our own mutex, we started with early versions of sqlite and had to come up with our own solution. I was planning on looking into using the built in mutex's, but first I need to solve the performance issues. The only difference I can see between our code and the CLI is it uses sqlite3_exec and a call back, while we do sqlite3_prepare Then we call sqlite3_column_count. We loop through 0-num_col and call: sqlite3_column_name sqlite3_column_decltype Then we do while(sqlite3_step() == SQLITE_ROW) >From my understanding the sqlite3_exec() is doing the same thing and sending the info too the callback. Any ideas? Thanks On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote: > "Philip Nick" <[EMAIL PROTECTED]> wrote: > > Greetings, > > > > Currently I am using sqlite3 in a multi-process/multi-threaded server > > setting. > > I use a Mutex to ensure only one process/thread can access the database > at > > one time. > > > > The current flow of events: > > Get Mutex > > Open Database connection > > Run Query > > Close Database connection > > Release Mutex > > > > SQLite does the mutexing automatically (assuming you are using > version 3.5.0 or later). > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > --------- > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Philip Nick E-Mail: [EMAIL PROTECTED] Cell: 352-262-9067 Web: http://www.ruffasdagut.com
[sqlite] sqlite3 performace
Greetings, Currently I am using sqlite3 in a multi-process/multi-threaded server setting. I use a Mutex to ensure only one process/thread can access the database at one time. The current flow of events: Get Mutex Open Database connection Run Query Close Database connection Release Mutex This seems to work well except I have noticed some performance issue when the database grows beyond a MB. I have noticed my program basically reads the whole database every time the query is run. The IO Read bytes increases by 900k for every query we run. We also chew a good chunch of the cpu I have tried keeping the database connection open and open/closing for every query. But there was no change. if I launch the CLI and run the query it runs instantly and monitoring the IO read bytes is see only ~20 bytes of read to execute the query, when my code is using over 900k for every call. I have been looking into the CLI source to see what is done differently, but was hoping someone on here might have some insight. Thanks Phil --
