Re: slow query with 7.31uc2

roswil@my-deja.com wrote: > This is from a subject on 12/1999. > Art Kagel wrote: > [Original thread SNIPPED] > 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 Whoa, that's a whole other ball of wax! I was just complaining about poor performance and the visissitudes<SP> of trying to tune the database stats and schema design. You're talking about getting incorrect results! Let me state for the record that there should be NO reason to get incorrect results and nothing we as DBAs should be able to do in the way of adding, dropping, upgrading, or downgrading stats levels, adding or dropping indexes, or tuning the engine's performance parameters that should cause the engine to return incorrect results! If you are seeing results that vary then one of three things is happening, either: o The query is pathological and returns different results depending on query path because there is something fundamentally WRONG with the query (like missing or incorrect join conditions), or o A table or some index or indexes is/are corrupted, or o You have found a BUG in the engine. Here are the steps you MUST take, preferrably in parallel for best resolution: 1) Post the errant queries here for others to look at along with platform and version info and SET EXPLAIN output and table schemas. 2) Run oncheck -cDI on ALL of the tables included in the troublesome queries. 3) Call tech support and tell them you have a PRIORITY 2 case (upgrade your existing case to priority 2 if you have already opened one. 4) Tell tech support to upgrade the case to PRIORITY 1 if not solved in 48 hours. At priority 1 the engineer assigned does not get to go home unless the case is resolved or a higher level engineer takes over. Things happen at priority level 1! 5) If not solved in 48 hours call tech support and ask to speak to a supervisor about the case. Tell him this is a business critical problem (I am assuming it is) and you want the case escalated. Informix support is the best in the industry, however, their natural attitude is: "If the customer does not care about this case why should we?" If you don't make noise about it and use the priority system (which is well documented in the support manual EVERY support customer gets and which is online) you don't get results! BTW Informix DOES care when the engine returns incorrect results. Note that this happened in two previous releases and both bugs were squashed within days of being reported to Informix, reporting customers had patched servers via FTP that same day, and a bug-fix release was generally available within a week. Contrast that to dBase IV which returned bad data for almost 6 months when it first went SQL or the original Oracle 6 or Sybase 10 (versions?) releases. Only the fact that few users had trusted the new versions enough to upgrade saved those companies. In contrast most of the Informix user base is on current releases or one release behind. Art S. Kagel