On 11 Apr 2012, at 5:01pm, Ian Katz <ifreeca...@gmail.com> wrote: > The Sqlite3 manual says that any locking operations affect the entire > database, not individual tables. > http://www.sqlite.org/lockingv3.html > > I was wondering if this effect could be compensated for by splitting > tables into separate databases and using the "attach database" option > outlined here: > http://stackoverflow.com/questions/6671678/objective-c-sqlite-join-tables-from-multiple-database
The technique will do what you want: lock only one table at a time. However, you may not have thought through what's really happening. Almost all the time taken by SQLite routines is time spent waiting for your hard disk to do something: either produce some data because of a read, or acknowledge a write command. Your two database files will be on the same hard disk. And a hard disk drive is not multi-threading: it takes one command at a time, processes that command, and returns the result. So even if you allow SQLite to access two tables at once, your commands will still delay one-another because they're waiting for the same hard disk to respond to their commands. For client/server data engines like MySQL, running on a server computer, with a persistent data cache in memory, locking at the table and/or row level can lead to great increases in speed: everything is just looking at RAM, and the hard disk is updated in the background. But SQLite does everything directly to the disk, and the disk is a bottleneck no matter how much you speed up your internal processing. I have not done speed tests on recent hardware (either rotating media or SSD) so I can't tell you how much the increase in speed actually is, but I will guess it's not much. Perhaps someone out there has done such testing and can tell us what they found. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
