On 12 Jul 2010, at 5:37am, raf wrote: > Each page hit results in several database writes each of which is currently > in a separate transaction. Is combining these into a single transaction likely > to help or make matters worse? Or is this just an inappropriate use of sqlite?
It will probably make it better. A transaction is an expensive time-consuming thing. Adding a single UPDATE or INSERT to a transaction you have to do anyway is cheaper. There are some other things you might want to do to improve speed. For instance, if you do lots of writes but few reads, it's best to reduce the number of indices you have to cover just the SELECTs you do frequently. On the other hand, if you do many SELECTs but few writes, it's worth checking to see that you understand how to make an INDEX ideal for each of your SELECT commands. You might also take a look at your schema, and try to work out ways of minimising the number of commands you have to execute. For instance, you may have toed the party line and normalised all your TABLEs so that data is never duplicated. But if you can save an entire INSERT command it might be worth duplicating a little information. Lastly, look at your functions. If it's easy, update to the most recent version of SQLite to fix bugs, which may well including bugs which slowed down locking. If you're using a library or framework instead of calling SQLite directly, consider swapping to another one, e.g. if you're writing in PHP and using the PDO module, consider switching to the SQLITE3 module. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
