RE: Problem with the sysmaster database.

I have often found that doing selective queries directly off of the sysmaster tables can take a long time. This is all to do with the fact that the sysmaster database is in fact a map onto some shared memory tables which aren't controlled by the normal lock mechanisms. What I prefer to do when I am working with the more dynamic sysmaster tables is to select everything form the table into a temp table and then select from the temp table. It is more of a bind as you keep having to drop the temp tables when you want to refresh them. Hope this helps Malcolm -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Jiten Thakkar Sent: 19 December 2005 19:01 To: informix-list@iiug.org Subject: Problem with the sysmaster database. Hi , Environment : Hardware : Sun4u-Sparc/Ultra O/S : Solaris 8 Informix : 9.30.FC2 ( 64 bit) I have a database with 40,000 tables. The data warehouse application creates new tables to store the information for new products. My problem is the query on SYSMASTER database is very slow. A simple select from syslocks ( select * from syslocks where tabname = <some_table> ) takes 5 minutes. I have run updates statistics on this table. My fact table has 2 million rows and the select for one of the rows comes back immediately. Can somebody suggest how to improve the performance of the SYSMASTER database ? Thanks, Jiten sending to informix-list sending to informix-list

Yes, don't forget to index and update stats on the temp tables.

Yes, don't forget to index and update stats on the temp tables. Check $INFORMIXDIR/etc/sysmaster.sql most of these things are views and join can do faster joins by querying the base tables directly.