I'll explain our needs a little more and then perhaps you can tell me if the current approach is the optimal one or not. In our application, if we were to have a single file, due to the billions of records we need to store, the file would easily exceed the 14TB maximum limit of a database file. In the application, the nature of the data is such that we really need many millions of tables, but the contents of each table is completely independent of another; we don't ever need to run a join or query multiple tables when making a query. We also know the exact table name + database to use for a query based on the table and database names. This is the reason we wanted to try and fit as many tables into a database file as practical. Each table is not expected to have more than 5000 rows. I saw some posts in the past where Dr. Richard Hipp said it's fine to have 100's or even 1000's of tables per database file as long as you keep the application that uses this database long running where you don't constantly open and close it. We would fit into this model. Performance is important in this app, so this is the reason we started using WAL mode to improve write speeds. 70% of the operations we have are inserts + updates. Any thoughts on this approach? Thanks
From: Simon Slavin <slav...@bigfraud.org> >To: Terry Cumaranatunge <cumar...@yahoo.com>; General Discussion of SQLite >Database <sqlite-users@sqlite.org> >Sent: Friday, September 2, 2011 12:41 PM >Subject: Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens >increase > > >On 2 Sep 2011, at 6:34pm, Terry Cumaranatunge wrote: > >> This does suggest that we should re-evaluate our design and not open as many >> databases from a single process. The original motivation was to limit the >> size the database for performance reasons, which resulted in a large number >> of database files. I think we are going to try creating multiple tables per >> database and evaluate its performance. >> >> Are there any general guidelines on a tested limit on the number of tables >> in a database or a general rule of thumb on the maximum recommended size for >> a database file? > >I'm concerned that you might be involved in premature optimization. The >performance of SQLite does not greatly degrade with larger tables or larger >databases. In fact you might be slowing your system down more by trying to >arrange multiple databases. Have you tried just using SQLite in the naive way >-- with one big table in one database -- and seeing whether that provides a >solution too slow for you ? > >Simon. > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
