Re: Performance Questions 7.x
Carol Stimmel (stimmel@fsl.noaa.gov) wrote: : HELP! : How long can it take?! A simple query in our database : is taking a L-O-N-G time to execute. It's not : our application, because we experience the same : problem from isql. We've fragmented our tables, set : read ahead parameters, and added a couple of : page cleaners. It is definitely having a problem : with the size of the database (which is indexed, maybe : not properly ;-). We have several dbspaces that : our broken out by our fragmentation scheme and one : big blobspace: : address chk/dbs offset size free bpages flags : 80737d20 15 15 511040 500000 ~202580 250000 POB /dev/informix-1 : Is this the problem? Is informix just a big slow pig? : Sorry for the name-calling, but I have exactly 2 weeks to : have this system screaming, and it's not. You're help : is greatly appreciated! BTW, when I mean slow, I'm talking : 15-30 seconds to execute an sql statement and return the : data. I have a requirement of 3-10 seconds. : What's left? We are running INFORMIX-OnLine Version 7.10.UD1 : on an HP-UX A.09.05 A 9000/755. I'm out of ideas, can : someone email or post their thoughts on how to improve : performance of our database. : -- : Carol L. Stimmel, Systems Analyst : NOAA/ERL/FSL work: stimmel@fsl.noaa.gov : 325 Broadway home: stimmel@majordomos.com : Boulder, CO 80303 Carol, Maybe you have tried this but just in case you have not..... 1) Take a look at the query optimizer's plan by executing "SET EXPLAIN ON" command. 2) Has an update statistics been performed recently. This recommendation was given to us in the INFORMIX Performance class: a) update statistics low b) time the query c) update statistics high on the filtered columns in the query d) time the query 3) If this query has a multi-table join, try breaking the query down to individual queries against the tables in the join. This may show you which part of the query is causing the problem. You then may be able to determine if there is a missing index, or an index that is not built in such a way that the optimizer would choose it, etc..... This is all assuming that the problem is the query. It sounds like you have looked at almost everything with regards to the engine. You may want to double check the checkpoint interval and duration but it sounds like you have isolated it down to a specific query. Good Luck -- +----------------------------+------------------------------------------+ | Barry Lemrow | Disclaimer: | | Technical Consultant | "Opinions expressed are mine and not | | SAP National Practice | necessarily those of my employer, | | Hewlett-Packard | family, or friends!" | | blemrow@a3166tpa.ssr.hp.com| | +----------------------------+------------------------------------------+