SQL - Optimizer issues

Hi! Try setting OPTCOMPIND to zero in onconfig. You have to stop/start engine for this to take effect. You can also set the environment variable OPTCOMPIND=0 for individual user (and of course export it). HTH Michael Rajesh wrote: > I have an SQL query in a 4GL program that joins three tables. Informix > version 7.24. The query chooses the correct path for one set of values in > the query. For another set of values, it chooses an entirely different > (wrong) path. The behaviour of the second query is not consistant. All of a > sudden, the second query starts working fine. Consequently the users have > split second response at some times and very long waits at the other. > > We have tried running these queries with "set explain on". We have tried to > use Update Statistics, Update statistics High etc. We have done oncheck -ci > on all three tables. We have also tried re-arranging the where clauses. > Nothing seems to work. Before now, I was under the impression that the > optimizer will decide the path based on the the structure of the query, the > join keys etc and will NOT vary depending on the values of the data items > within the query. > > How best can I address such a problem? > > Regards!!!

I have an SQL query in a 4GL program that joins three tables. Informix version 7.24. The query chooses the correct path for one set of values in the query. For another set of values, it chooses an entirely different (wrong) path. The behaviour of the second query is not consistant. All of a sudden, the second query starts working fine. Consequently the users have split second response at some times and very long waits at the other. We have tried running these queries with "set explain on". We have tried to use Update Statistics, Update statistics High etc. We have done oncheck -ci on all three tables. We have also tried re-arranging the where clauses. Nothing seems to work. Before now, I was under the impression that the optimizer will decide the path based on the the structure of the query, the join keys etc and will NOT vary depending on the values of the data items within the query. How best can I address such a problem? Regards!!!

Hi! We had several problems like yours with the version 7.24. Having upgraded on 7.30 we had much more. Sometimes we can get a query traced and you see the path chosen by the optimizer is incorrect. It does not want to take the correct index. We have great SAP R/3 shops with Informix. With R/3 you have practically no chance to influence the actual physical queries. We suffer from poor performance and we have no solution. We think it is all because of the optimizer. I mean you cannot trace everything. What can you do with a fall like this: a query had average response time of 4 sec. On a monday it suddenly ran longer than an hour. We got it traced and we saw same old story: incorrect path chosen by optimizer. 3 days earlier it was still OK! You can try to play with different levels of update statistics, high for all columns or low with drop distributions - it may help, but no guarantee. But for god's sake! R/3 has appr. 10000 tables! You can not tune each of them casually. Of course, we have got notes and hints and casual solutions. But what we need is a correct optimizer. We hope it comes with 7.31. Regards Peter Rajesh schrieb: > I have an SQL query in a 4GL program that joins three tables. Informix > version 7.24. The query chooses the correct path for one set of values in > the query. For another set of values, it chooses an entirely different > (wrong) path. The behaviour of the second query is not consistant. All of a > sudden, the second query starts working fine. Consequently the users have > split second response at some times and very long waits at the other. > > We have tried running these queries with "set explain on". We have tried to > use Update Statistics, Update statistics High etc. We have done oncheck -ci > on all three tables. We have also tried re-arranging the where clauses. > Nothing seems to work. Before now, I was under the impression that the > optimizer will decide the path based on the the structure of the query, the > join keys etc and will NOT vary depending on the values of the data items > within the query. > > How best can I address such a problem? > > Regards!!!

Rajesh wrote: > > I have an SQL query in a 4GL program that joins three tables. Informix > version 7.24. The query chooses the correct path for one set of values in > the query. For another set of values, it chooses an entirely different > (wrong) path. The behaviour of the second query is not consistant. All of a > sudden, the second query starts working fine. Consequently the users have > split second response at some times and very long waits at the other. > ... You may see these kinds of problems even in the 9.x line - we have. The only solution we have found to consistently make a difference is to tweak the query: select foo from bar where x = "X" and x = "X" and x = "X" and y = "Y" Oddly enough this more or less forces the correct path. You may need to experiment, but you get the gist... Jan