On 2016/09/14 8:29 PM, Alex Ward wrote:
We currently have 500 tables and 1500 triggers in the schema. Perhaps that is why we didn't have much luck having one connection per thread or opening a connection per access. Perhaps our schema needs a rework, would one table with a million rows be better than 500 tables with 2000 rows each?
YES. In 99% of cases it is far more better to have x million rows in a single table than trying to use multiple tables. It is the job of the DB engine to efficiently handle large tables, and sqlite (like most other DB engines) does a brilliant job of it because it is the Alpha use-case and the single biggest focus of efforts to enhance during development. There are cases when a dispersed schema might be superior, but they are few and obscure. 1 Table = 1 set of Indices, 1 set of triggers, 1 set of views.
Perhaps your case was different, but mostly when people do something like this multiple-table thing, it is because the try to think for the DB engine, and assume the multiple table way is better (maybe it just "feels" better), but upon testing, you will find the amount of effort to run multiple tables outweighs any gain in access speed significantly. Also, a B-Tree Index works far better on one large table than many B-Tree indices on many tables. The increase in time taken to hit a specific PK in a large table diminishes rapidly with table growth. (In simple binary terms, IIRC, it takes 5 lookup steps to hit a PK in just 30 rows, yet only 24 look-up steps to hit a PK in 1 million rows, 25 to hit it in 2 mil rows, etc. - Law of diminishing returns in action)
We were considering adding a view per table too, that would make it 3000 elements in the schema, if 100 is considered a lot are we attempting to use sqlite in a manner that it is not suited for? We may need to consider another alternative if that's the case, although that would be a shame.
This alone should be enough of a reason to reconsider.
Sadly we are not comparing sqlite with another SQL DB. We are attempting to replace an in-house memory resident non-sql database with sqlite. So our comparison is between a) reading from memory and b) opening a connection and reading from sqlite.
This is perfect, it's what SQLite was born to do. Just trust SQLite to do it's thing and don't try to pre-empt how it will fare and prematurely optimize by distributed schemata and the like. Take the simplest route first, if that turns out to really be too slow, /then/ perhaps ask what can be done to improve, given the data/schema specifics.
(And yes, we are not oblivious to the fact that you may have already invested insane amounts of time in doing it the other way, don't throw away the code yet, just try the normal way also)!
Good luck! Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
