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
