Re: Max number of tables

>Date: Tue, 10 Aug 1993 20:02:50 -0600 >From: hanan@salsa.abq.bdm.com >Subject: Re: Max number of tables >>>From: uunet!hpsgm2.sgp.hp.com!chanm (Michael CHAN) >>>Subject: Max number of tables >>>Date: Wed, 4 Aug 1993 08:40:58 GMT >>>X-Informix-List-Id: <news.3999> >>> We currently are thinking of a design where there will be 2400 database >>> tables. We like to know whether Informix has any upper limit on the >>> number of tables, the size of each table, and whether there will be >>> any adverse impact on the search performance if there are so many tables. >>Using SE or OnLine? Actually, neither engine imposes an upper limit on >>the number of tables. >>Obviously, there will be some slight degradation of performance, >>because searches on the system catalogue will have to negotiate more >>index information to get at the description of a table, and I have to >>assume that more tables will be in use at any given time, so that >>cache rates etc will be affected. But this should not be very >>noticeable, and would affect any system handling that design. >>Why does your design need so many tables? How is anyone going to >>understand it? I would not like to have to deal with a database >>containing 2400 tables. >As a follow-up to this question, in general would better performance be >gained in a database, with many automated transactions but few on-line >users (updates, report processes, statistical queries, etc.), where there >would be, say, 500 tables each with about 106,800 entries, or say, 12 >tables each with 4,450,000 entries? Is there a performance curve from >Informix which compares numbers of entries against numbers of tables? I have never done the necessary measurements to be sure, but I would think that the smaller number of large tables is likely to be easier to handle programmatically, and would have equivalent or better performance. One reason for the better performance is that if you have 500 tables, each with 100k rows, then when you are using a index to access those tables, you will have up to 500 different root index nodes in shared memory, as well as the lower level nodes, whereas with one table, you'd only have the one (much busier) root node. That releases 499 pages of valuable shared memory for other purposes. The arithmetic is over-simplified, but I think the idea is valid. The only major advantage of the multi-table scenario is that your code will be forced to prepare the statements which operate on the tables, which is likely to lead to better performance provided that the same user repeatedly refers to the same table. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>