Re: Performance Questions 7.x
stimmel@fsl.noaa.gov (Carol Stimmel) 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 Its real hard to give any advice with no more data than you provided, but don't assume its not the application because bad sql is bad if it is ran using application code or if it is using isql. We had some sql that was taking 30 to 40 minutes to run and we changed an or in the sql to an union and the query ran in 4 secs. No database tuning at all. As a DBA we had to tune the sql to show the application it was not the engine or the database causing the problem. If it is good sql you might look at the possiblilty that your tables you are fragmented are really to small to be a good fragmentation candidate. With talking to our informix reps tables < 1 gig in size usually cost you in performance if you fragment. The table is not big enough to pay for the overhead of fragmentation. -- Tolly Brumbelow, Informix DBA email:tolly.brumbelow@bridge.bellsouth.com