Informix 9 and correlated subqueries
After upgrading from Informix 7.31 to 9, a user found some correlated subqueries ran fine in dbaccess but slowly from applications, suspecting the v9 optimizer was worse at flattening subqueries into joins. Art Kagel said 9.40's optimizer is generally better, and that dbaccess-vs-application differences usually stem from host/replaceable parameter values versus hard-coded literals, suggesting UPDATE STATISTICS and comparing SET EXPLAIN output from both. Others mentioned enabling explain on the fly via onmode, asked to see the SQL, and noted an environment variable that disables subquery flattening (used to avoid an assert-fail bug in 9 and 10). No confirmation of a fix from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We recently upgraded to Informix v 9 from 7.31 with one of our
customers, and we are having trouble with some correlated subqueries.
In dbaccess they appear to run fine, but under some processes they run
slowly.
We suspect a poor query plan in these cases, as they seem quite
consistent - is it possible under some circumstances that Informix is
inferior in spotting subqueries that can be flattened into joins in v 9
than in 7?
These queries always ran quickly under 7.
ian.miell@gmail.com wrote:
> We recently upgraded to Informix v 9 from 7.31 with one of our
> customers, and we are having trouble with some correlated subqueries.
>
> In dbaccess they appear to run fine, but under some processes they run
> slowly.
>
> We suspect a poor query plan in these cases, as they seem quite
> consistent - is it possible under some circumstances that Informix is
> inferior in spotting subqueries that can be flattened into joins in v 9
> than in 7?
>
> These queries always ran quickly under 7.
The optimizers are certainly different, but in general 9.40 does a better
job than 7.31 on most complex queries. Usually when a performance
difference happens between running a query in dbaccess/sqlcmd and from an
application it has to do with replacable parameters and the specific values
the app is supplying to them versus the hard coded values you are entering
with the query into the query tool. This usually means that an update
statistics session is needed. If you can have the application SET EXPLAIN
ON before the query and compare that result to doing the same in dbaccess
you can confirm this hypothesis and at least get a better idea of what's
going on.
Art S. Kagel
if i am not mistaken one can set explain on for a session on the fly
nowdays.
checkout the rel notes for onmode (some option.... sorry forgot the
exact one)
Superboer.
Art S. Kagel schreef:
> ian.miell@gmail.com wrote:
> > We recently upgraded to Informix v 9 from 7.31 with one of our
> > customers, and we are having trouble with some correlated subqueries.
> >
> > In dbaccess they appear to run fine, but under some processes they run
> > slowly.
> >
> > We suspect a poor query plan in these cases, as they seem quite
> > consistent - is it possible under some circumstances that Informix is
> > inferior in spotting subqueries that can be flattened into joins in v 9
> > than in 7?
> >
> > These queries always ran quickly under 7.
>
> The optimizers are certainly different, but in general 9.40 does a better
> job than 7.31 on most complex queries. Usually when a performance
> difference happens between running a query in dbaccess/sqlcmd and from an
> application it has to do with replacable parameters and the specific values
> the app is supplying to them versus the hard coded values you are entering
> with the query into the query tool. This usually means that an update
> statistics session is needed. If you can have the application SET EXPLAIN
> ON before the query and compare that result to doing the same in dbaccess
> you can confirm this hypothesis and at least get a better idea of what's
> going on.
>
> Art S. Kagel
Could you please send us the SQL code?
ian.miell@gmail.com wrote:
>We recently upgraded to Informix v 9 from 7.31 with one of our
>customers, and we are having trouble with some correlated subqueries.
>
>In dbaccess they appear to run fine, but under some processes they run
>slowly.
>
>We suspect a poor query plan in these cases, as they seem quite
>consistent - is it possible under some circumstances that Informix is
>inferior in spotting subqueries that can be flattened into joins in v 9
>than in 7?
>
>These queries always ran quickly under 7.
>
>_______________________________________________
>Informix-list mailing list
>Informix-list@iiug.org
>http://www.iiug.org/mailman/listinfo/informix-list
>
>
>
--
Yunyao "Frank" Qu
Computer Sciences Corporation(CSC)
NOAA/CLASS, (301)817-4696
ian.miell@gmail.com wrote:
> We recently upgraded to Informix v 9 from 7.31 with one of our
> customers, and we are having trouble with some correlated subqueries.
>
> In dbaccess they appear to run fine, but under some processes they run
> slowly.
>
> We suspect a poor query plan in these cases, as they seem quite
> consistent - is it possible under some circumstances that Informix is
> inferior in spotting subqueries that can be flattened into joins in v 9
> than in 7?
>
> These queries always ran quickly under 7.
It is late for me but I think recently there was some discussion of an
environment variable that flattened or didn't flatten correlated
subqueries. I can't remember if it was 10, 9 or if it applies. You can
probably search the group or someone might remember it better. Or did I
hear about it at the Tampa convention. Can't quite remember, sorry.
"bozon" <curtis@crowson1.com> wrote in message news:1154053215.360667.118960@s13g2000cwa.googlegroups.com... > It is late for me but I think recently there was some discussion of an > environment variable that flattened or didn't flatten correlated > subqueries. I can't remember if it was 10, 9 or if it applies. You can > probably search the group or someone might remember it better. Or did I > hear about it at the Tampa convention. Can't quite remember, sorry. There's an environment variable that *disables* sub-query flattening, and thereby avoids a bug in 9 and 10 that can give assert fails. Could that be what you are thinking of?