Performance Problem SE with too many tables
Posted in 1997
Topics: Performance & Tuning, Platform-Specific Issues
I have recently noticed a serious performance problem with my SE 7.11 engine. I logged the problem with Informix Tech Support, but they have not gotten back to me in over 2 weeks, even after leaving several messages. But this is probably a discussion in itself for another newsgroup thread. Anyway, I have a database with about 100 tables. Avergage number of rows in each table is less than 5,000. Recently I have added a few more large tables, about 300,000 rows. Ever since I created these tables I have noticed serious performance problems with tables in this database with more then 5,000 rows. When I run a status on the table with 300,000 rows it takes up to 4 minutes to return the information. As a goof, I created these same 3 tables in another database and the isql status command returns immediately. All databases are on the same disk partition and I have already checked all of the index files. I then cloned the databases with 100 tables in it and started deleting tables and testing the results. Every couple of tables that I deleted, I noticed better performance with the tables in questions. After reducing the number of tables below 25 the performance seemed normal. Does anyone have a clue about this problem?? Does anyone know of any limitations with the Informix SE engine that would cause such a problem??? My configuration is a Sparc 20 (2 processors) Solaris 2.4 and 256 MB of RAM