Folks. Our application uses SQlite on Mac OSX. It is a central data hub for a larger infrastructure and manages data coming in from some clients, and requests for data from other clients, using our own XML based protocols via TCPIP.
Its somewhat like a web server with a backend system supplying data presented to web clients. All client access requests in and out of the system are made on individual threads. Our approach for SQLite has been to open the database on our main thread, begin a transaction, then using a locking mechanism, hand the database connection to threads in turn, then once every 60 seconds we flush the connection by ending the transaction then closing the DB(and making a backup copy) and finally reopening the database. Whilst this approach seems to work reliably, I am concerned that it may not be the most efficient model, and in some cases we are seeing very heavy load on our application, where this technique could benefit from some optimisations, since the database access becomes a bottleneck. I am interested to learn from other SQLite developers: - Is there a faster way to flush to disk and backup the database ? - We don't currently prepare SQL statements in advance, would this technique benefit from somehow preparing statements *before* that thread gets the lock on the database ? Can we have multiple threads using the SAME database connection preparing SQL Queries at the same time (so long as only one is RUNNING the query at once ?). Its tempting to consider using a multi-user database instead, but for the most part SQLite is doing a great job, and we don't really think the complexity of something like mySQL is warranted. All access to the database is made from multiple threads in one application. Any other suggestions on ways we could optimize the way we interact with SQLite ? Thanks. Mark. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
