On 2 Oct 2009, at 8:40pm, Mike Shal wrote: > Ahh, ok - that makes sense. Does this locking overhead not occur on > other platforms (I've only tried linux -- gentoo and ubuntu), or if > sqlite is built differently?
It's to do with how the platform (OS and hard disk format) support file locking. Those two things together determine what really happens when SQLite uses the C call that locks a file. So even saying 'Gentoo' is too general, you want to know what format the disk your database is on has too. > It seems a bit disingenuous to claim > there will be no performance gain by putting selects in a transaction, > when sqlite clearly does less work with the transaction (in the form > of not getting the read lock multiple times). I've used SQLite for multivariate analysis. The sort of thing where you collect many variables worth of data on lots of cases, then analyse every possible combination of variables because you are looking for /anything/: you didn't have a strong idea of what to look like when you set out, you just want to know if there's anything interesting in the data you got. A census of 25 questions gives you 25 * 24 = 600 SELECT commands. That the first simple step, and you're unlikely to find anything you didn't already suspect. Then you try every combination of three variables: 25 * 24 * 23 = 13800 SELECT commands. If you still get nothing significant and are scared you're going to lose your funding you try 25 * 24 * 23 * 22 = 303600. And all this data is unchanging: might was well be on a data DVD (and data DVD drivers don't implement file locking !). So yes, thousands of consecutive SELECT commands is a perfectly normal use of SQLite. And until you raised the subject it hadn't occurred to me that it was necessary to lock on a SELECT but thinking about it, it's obvious. Duh. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
