Philip Nick wrote:
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]
-----------------------------------------------------------------------------
We have an RPC server. When it gets a request it looks at a pool of
threads and selects the most recently opened on the basis that it is
still in cache. If the pool is empty it launches a thread and opens an
Sqlite connection to it. Each thread has an Sqlite context and an open
Sqlite connection.
Dormant threads wait on an event and are signalled to start execution.
The performance is quite good with RPC's executing in about 40mS as
recorded by the browser. There is no churning from opening and closing
connections and creatiing and destroying threads.
On shutdown the Sqlite connections are closed and the threads destroyed.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
