slow query with 7.31uc2

This is from a subject on 12/1999. Art Kagel wrote: >> Forum: comp.databases.informix >> Thread: Slow query with 7.31UC2 >> Message 6 of 8 Subject: Re: Slow query with 7.31UC2 Date: 12/21/1999 Author: Art S. Kagel <kagel@bloomberg.net> << previous in thread ' next in thread >> I had a similar problem just yesterday with our PeopleSoft database which we were doing only UPDATE STATISICS HIGH per PeopleSoft's recommendation. A query that was running less than a minute on IDS 7.23 was taking almost an hour in 7.30UC2. Some HUGE complex query with 12 main tables in each of two UNIONed SELECTs each of which had four filters depending on subqueries. In the sqexplain file 7.32 estimated a cost of ~3500 and 2 rows returned (which was accurate as to the number of rows) while 7.30 estimated a cost of 8 and 2 rows returned but takes 100 times as long to process. Xtree showed the query stalled on a repeated sort in a lower level join (go try to figure out which joined pair 8-{). Tried everything, finally in frustration I tried dostats and the query was almost instantaneous! Now Drumzspace has had a similar problem with the dostats generated stats and no distributions is the solution. How is one to know how to tune the server and databases for complex apps like PeopleSoft, Baan, SAS, etc.? I'm back to what I used to say to Menlo in the early days of IDS "I know more about this engine than most folk out there and I don't know what the ^%$# I'm doing half the time!". Don't get me wrong, I'm getting the job done, as I did in this case, and I probably would have stumbled into Drumzspace's solution as well, but I'm doing just that - stumbling in the dark looking for my glasses and navigating around the furniture by experience alone. What chance do new users and DBAs have? Just needed to vent. Thanks. Art S. Kagel We have had our fair share of problems with SQL performance and wrong rows returned. I echo your words, "What chance do new users and DBAs have? " What chance do any of us have when we are blind-sided by SQL results that can change without warning? Has Informix acknowledged how serious this is? Is Informix in any way strongly compelled to address this issue Sent via Deja.com http://www.deja.com/ Before you buy.