Re: Performance Problem SE with too many tables
Robert Arciere wrote: > > 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 Can I refer you to the first three rules for performance tuning: 1. UPDATE STATISTICS 2. UPDATE STATISTICS 3. UPDATE STATISTICS Hope that helps, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+