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, and disk space is the most likely cause of trouble. (The }fussy will point out that 2**31-101 is the theoretical maximum number }of tables.) If you are using OnLine, you will want to alter the next }extent size of the system catalogue tables to take into account the }number of entries: use ALTER TABLE Syscolumns MODIFY NEXT SIZE 64 or }whatever extent size seems appropriate to you -- I haven't worked out }how big the extents should be. } }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. } }Yours, }Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> 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? Thanks. Jeffrey D. Hanan hanan@abq.bdm.com Phone:(505) 848-5362 Fax: (505) 848-5720