Re: Performance Questions 7.x
On Mar 6, 6:33pm, Carol Stimmel wrote: > Subject: Performance Questions 7.x > 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 It's almost impossible to provide help with so little detail to go on. To do a good job would probably require a few days time from an Informix consultant to understand your database layout and identify the reasons for the performance bottle neck. A few things you can check is set explain on your query. This should tell you whether it is accessing the tables in the expected order and using reasonable indexes. One other area to consider is your fragmentation policy. Sometimes it can be better not to fragment the database or to create external indexes either not fragmented or fragmented under different rules. The reason for this is that if an internal index is being used that is not also the fragmentation strategy then the engine has to scan the index in each fragment to see if rows matching the query exists. Then it has to read the rows from each fragment and sort them into the correct order. If this is your problem then test having external indexes though be warned there were some issues on performance of external indexes. I don't know if those have all been fixed. Alternatively drop fragmentation entirely until you can prove that the fragmentation strategy helps your performance rather than hurting it. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!