On 5 May 2012, at 12:22pm, Marcolippo Polpettino <sardin...@hotmail.it> wrote:
> I've read that sqlite locks the entire database file on updates. True. This is extremely fast: checking for a lock involves checking just one thing. Making a lock involves changing just one thing. Table-level and row-level locking requires more checking and changes, so it takes longer. > Our database is based on a very small number of tables that contain a huge > amount of records (under WAL mode). Probably the right way to do it, if you are going to use SQLite at all. > Will we get improvements in performance if we create different database files > (one for each table) and attach them to a single connection? Not if databases will still be stored on the same hard disk drive. The majority of time taken by SQL commands is spent waiting for your physical hardware -- the hard disk. It can only do one thing at one time. If you need to speed up manipulation of a low number of tables, put each table on a different hard disk. > Will the files be locked indipendently? Yes. You may get an increase in speed. But how much it is depends on so many tiny little factors about your particular setup we can't predict it. And it's not certain that you'll get any increase at all. But overall your post does make me wary of premature optimization. Rather than waste your time making your application as fast as possible, try it and decide whether it's fast enough. You can spend twenty hours rewriting your software to speed up your application, only to find that the slower version was just as acceptable, and now you have very complicated source code which is hard to debug. If every tiny increment in speed is important to you, it might be better to switch to a server/client DBMS like Postgres, since those cache data in memory and can write changes to disk in the background. SQLite is designed with other criteria in mind: platform-independence and simplicity. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
