LEFT JOIN vs. OUTER -- Performance Problem

Posted in 2012

Summary

Richard found that a query using ANSI LEFT JOIN ran over a minute while the equivalent Informix OUTER-join syntax returned instantly, but only when two of the joined tables lived in a database on another Informix instance (accessed via @server). Marco Greco suggested the WHERE s.persnr > 0 was being applied as a post-join filter and proposed moving it into the ON clause or a derived table; that fixed correctness but not speed. Everett Mills diagnosed the real cause: the attribut = 'FARZ'/'PROM' filters on the remote table were applied only after rows were shipped to the local server. Wrapping each remote table in a sub-select containing its filter cut the runtime to under a second.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Performance & Tuning, SQL Development & Query Writing, Platform-Specific Issues

rspitz.md@googlemail.com — Wed, 7 Mar 2012 08:17:28 -0800 (PST) — source: Usenet: comp.databases.informix

Dear Informixers, why is a query containing outer joins written as LEFT JOIN so much slower than when written using the Informix-specific OUTER syntax, as soon as the query includes tables from another database in another Informix instance on the same server? Here is a test case. This query returns instantly: SELECT s.persnr, s.vollname, vt.v_end AS ver_ende, fa.von AS farzt_datum, dr.von AS doktr_datum FROM stamm s, OUTER vertrag vt, OUTER verteiler@opserver_tcp:pers_zusatz fa, OUTER verteiler@opserver_tcp:pers_zusatz dr WHERE s.persnr > 0 AND vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) AND fa.persnr = s.persnr AND fa.attribut = "FARZ" AND dr.persnr = s.persnr AND dr.attribut = "PROM" ORDER BY s.persnr DESC while this query takes over 1 minute to return identical results: SELECT s.persnr, s.vollname, vt.v_end AS ver_ende, fa.von AS farzt_datum, dr.von AS doktr_datum FROM stamm s LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr AND fa.attribut = "FARZ") LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr AND dr.attribut = "PROM") WHERE s.persnr > 0 ORDER BY s.persnr DESC The tables involved are rather small: stamm: 985 rows vertrag: 5214 rows verteiler@opserver_tcp:pers_zusatz: 14963 rows In the test environment where the table "pers_zusatz" was in the same database as the other two tables, there were no performance problems. Database engine is IDS 11.50UC7GE running on SuSE Linux Enterprise Server 10 SP1 32bit. I didn't write the original "LEFT JOIN" query, the application developer asked me for help as the database admin. Re-writing the query using the OUTER syntax solved the problem for now, but I'm still puzzled. Any ideas? Regards, Richard

↪ replying to rspitz.md@googlemail.com

Marco Greco — Wed, 07 Mar 2012 17:22:40 +0000 — source: Usenet: comp.databases.informix

On 07/03/12 16:17, rspitz.md@googlemail.com wrote: > Dear Informixers, > > why is a query containing outer joins written as LEFT JOIN so much slower than when written using the Informix-specific OUTER syntax, as soon as the query includes tables from another database in another Informix instance on the same server? > > Here is a test case. This query returns instantly: > > SELECT > s.persnr, > s.vollname, > vt.v_end AS ver_ende, > fa.von AS farzt_datum, > dr.von AS doktr_datum > FROM stamm s, OUTER vertrag vt, > OUTER verteiler@opserver_tcp:pers_zusatz fa, > OUTER verteiler@opserver_tcp:pers_zusatz dr > WHERE s.persnr> 0 > AND vt.persnr = s.persnr > AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) > AND fa.persnr = s.persnr > AND fa.attribut = "FARZ" > AND dr.persnr = s.persnr > AND dr.attribut = "PROM" > ORDER BY s.persnr DESC > > while this query takes over 1 minute to return identical results: > > SELECT > s.persnr, > s.vollname, > vt.v_end AS ver_ende, > fa.von AS farzt_datum, > dr.von AS doktr_datum > FROM stamm s > LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) > LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr AND fa.attribut = "FARZ") > LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr AND dr.attribut = "PROM") > WHERE s.persnr> 0 > ORDER BY s.persnr DESC > > The tables involved are rather small: > stamm: 985 rows > vertrag: 5214 rows > verteiler@opserver_tcp:pers_zusatz: 14963 rows > > In the test environment where the table "pers_zusatz" was in the same database as the other two tables, there were no performance problems. > > Database engine is IDS 11.50UC7GE running on SuSE Linux Enterprise Server 10 SP1 32bit. > > I didn't write the original "LEFT JOIN" query, the application developer asked me for help as the database admin. Re-writing the query using the OUTER syntax solved the problem for now, but I'm still puzzled. Any ideas? > > Regards, Richard > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Your proble is the WHERE s.persnr> 0 filter. In ANSI joins, this is evaluated as a post join filter, which means that all the joins get materialized first and then the filter gets applied. Moove it to the filters in the first join and skip the where clause entirely - that should fix it. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to Marco Greco

Richard Spitz — Thu, 8 Mar 2012 01:33:53 -0800 (PST) — source: Usenet: comp.databases.informix

Am Mittwoch, 7. März 2012 18:22:40 UTC+1 schrieb Marco Greco: > > Your proble is the > > WHERE s.persnr> 0 > > filter. > In ANSI joins, this is evaluated as a post join filter, which means that all > the joins get materialized first and then the filter gets applied. > Moove it to the filters in the first join and skip the where clause entirely I read about the post join filters in ANSI LEFT JOINs when I googled for a solution, but I just can't figure out where to apply the "WHERE s.persnr > 0" filter so I really only get results where this condition is met. Could you tell me how to write the above query with the filter in the first join? When I leave out this condition altogether, the run time for the query is cut into half from 90 to 45 seconds, but this is still way too much. Regards, Richard

↪ replying to Richard Spitz

Marco Greco — Thu, 08 Mar 2012 12:14:16 +0000 — source: Usenet: comp.databases.informix

On 08/03/12 09:33, Richard Spitz wrote: > Am Mittwoch, 7. März 2012 18:22:40 UTC+1 schrieb Marco Greco: >> >> Your proble is the >> >> WHERE s.persnr> 0 >> >> filter. >> In ANSI joins, this is evaluated as a post join filter, which means that all >> the joins get materialized first and then the filter gets applied. >> Moove it to the filters in the first join and skip the where clause entirely > > I read about the post join filters in ANSI LEFT JOINs when I googled for a solution, but I just can't figure out where to apply the "WHERE s.persnr> 0" filter so I really only get results where this condition is met. Could you tell me how to write the above query with the filter in the first join? > > When I leave out this condition altogether, the run time for the query is cut into half from 90 to 45 seconds, but this is still way too much. > > Regards, Richard > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > This should do it SELECT s.persnr, s.vollname, vt.v_end AS ver_ende, fa.von AS farzt_datum, dr.von AS doktr_datum FROM stamm s LEFT JOIN vertrag vt ON (s.persnr > 0 AND vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr AND fa.attribut = "FARZ") LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr AND dr.attribut = "PROM") ORDER BY s.persnr DESC Also, do not forget to compare ANSI and non ANSI query plans. It wouldn't be the first time they are optimized differently. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to Marco Greco

Richard Spitz — Thu, 8 Mar 2012 08:05:18 -0800 (PST) — source: Usenet: comp.databases.informix

Am Donnerstag, 8. März 2012 13:14:16 UTC+1 schrieb Marco Greco: > This should do it > > SELECT > s.persnr, > s.vollname, > vt.v_end AS ver_ende, > fa.von AS farzt_datum, > dr.von AS doktr_datum > FROM stamm s > LEFT JOIN vertrag vt ON (s.persnr > 0 AND vt.persnr = s.persnr AND vt.v_end > = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) > LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr > AND fa.attribut = "FARZ") > LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr > AND dr.attribut = "PROM") > ORDER BY s.persnr DESC Thanks for your suggestion, but I had tried the same before and it didn't solve the problem. Not only does this query return records with persnr < 0, but it is just as slow as when omitting the post join filter "persnr > 0" altogether. > Also, do not forget to compare ANSI and non ANSI query plans. It wouldn't be > the first time they are optimized differently. Posting the output of "set explain on" would be a bit much in this newsgroup, but I'd be happy to send it by mail to anybody willing to look at it. Regards, Richard

↪ replying to Richard Spitz

Marco Greco — Thu, 08 Mar 2012 17:45:48 +0000 — source: Usenet: comp.databases.informix

On 08/03/12 16:05, Richard Spitz wrote: > Am Donnerstag, 8. März 2012 13:14:16 UTC+1 schrieb Marco Greco: >> This should do it >> >> SELECT >> s.persnr, >> s.vollname, >> vt.v_end AS ver_ende, >> fa.von AS farzt_datum, >> dr.von AS doktr_datum >> FROM stamm s >> LEFT JOIN vertrag vt ON (s.persnr> 0 AND vt.persnr = s.persnr AND vt.v_end >> = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) >> LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr >> AND fa.attribut = "FARZ") >> LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr >> AND dr.attribut = "PROM") >> ORDER BY s.persnr DESC > > Thanks for your suggestion, but I had tried the same before and it didn't solve the problem. Not only does this query return records with persnr< 0, but it is just as slow as when omitting the post join filter "persnr> 0" altogether. > >> Also, do not forget to compare ANSI and non ANSI query plans. It wouldn't be >> the first time they are optimized differently. > > Posting the output of "set explain on" would be a bit much in this newsgroup, but I'd be happy to send it by mail to anybody willing to look at it. > > Regards, Richard this definitely applies the filter on stamm before any join, although chances are that the subquery on stamm might be materialized: SELECT s.persnr, s.vollname, vt.v_end AS ver_ende, fa.von AS farzt_datum, dr.von AS doktr_datum FROM (select * from stamm where stamm.persnr>0) s LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr AND fa.attribut = "FARZ") LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr AND dr.attribut = "PROM") ORDER BY s.persnr DESC -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

↪ replying to Marco Greco

Richard Spitz — Mon, 12 Mar 2012 09:15:28 -0700 (PDT) — source: Usenet: comp.databases.informix

Am Donnerstag, 8. März 2012 18:45:48 UTC+1 schrieb Marco Greco: > this definitely applies the filter on stamm before any join, although chances > are that the subquery on stamm might be materialized: > > SELECT > s.persnr, > s.vollname, > vt.v_end AS ver_ende, > fa.von AS farzt_datum, > dr.von AS doktr_datum > FROM (select * from stamm where stamm.persnr>0) s > LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) > LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr = s.persnr > AND fa.attribut = "FARZ") > LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr = s.persnr > AND dr.attribut = "PROM") > ORDER BY s.persnr DESC Thanks again, this time the query delivers the correct results but performs just as badly as my original version. The main question remains: Why does LEFT JOIN performance suck as soon as the query involves tables from another server? Regards, Richard

↪ replying to Richard Spitz

Everett Mills — Mon, 12 Mar 2012 12:13:59 -0500 — source: Usenet: comp.databases.informix

Richard- My best guess is that it's applying your fa.attribut = "FARZ" and dr.attribut = "PROM" filters after the rows are brought to the querying server. Try this instead. I think it should prevent the excess traffic: SELECT s.persnr, s.vollname, vt.v_end AS ver_ende, fa.von AS farzt_datum, dr.von AS doktr_datum FROM (select * from stamm where stamm.persnr>0) s LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) LEFT JOIN ( SELECT von, persnr FROM verteiler@opserver_tcp:pers_zusatz fa WHERE attribute = "FARZ" ) fa ON (fa.persnr = s.persnr) LEFT JOIN ( SELECT von, persnr FROM verteiler@opserver_tcp:pers_zusatz WHERE attribute = "PROM" ) dr ON (dr.persnr= s.persnr) ORDER BY s.persnr DESC --EEM If you need fa and dr to be indexed, load them into temp tables, index them and join the temp tables instead. --EEM >-----Original Message----- >From: informix-list-bounces@iiug.org [mailto:informix-list- >bounces@iiug.org] On Behalf Of Richard Spitz >Sent: Monday, March 12, 2012 11:15 AM >To: informix-list@iiug.org >Cc: informix-list >Subject: Re: LEFT JOIN vs. OUTER -- Performance Problem > >Am Donnerstag, 8. März 2012 18:45:48 UTC+1 schrieb Marco Greco: >> this definitely applies the filter on stamm before any join, although >> chances are that the subquery on stamm might be materialized: >> >> SELECT >> s.persnr, >> s.vollname, >> vt.v_end AS ver_ende, >> fa.von AS farzt_datum, >> dr.von AS doktr_datum >> FROM (select * from stamm where stamm.persnr>0) s >> LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = >(SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) >> LEFT JOIN verteiler@opserver_tcp:pers_zusatz fa ON (fa.persnr >= s.persnr >> AND fa.attribut = "FARZ") >> LEFT JOIN verteiler@opserver_tcp:pers_zusatz dr ON (dr.persnr >= s.persnr >> AND dr.attribut = "PROM") >> ORDER BY s.persnr DESC > >Thanks again, this time the query delivers the correct results but >performs just as badly as my original version. > >The main question remains: Why does LEFT JOIN performance suck as soon >as the query involves tables from another server? > >Regards, Richard >_______________________________________________ >Informix-list mailing list >Informix-list@iiug.org >http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to Everett Mills

Richard Spitz — Wed, 14 Mar 2012 09:34:52 -0700 (PDT) — source: Usenet: comp.databases.informix

Am Montag, 12. März 2012 18:13:59 UTC+1 schrieb Everett Mills: > Richard- > My best guess is that it's applying your fa.attribut = "FARZ" and dr.attribut = "PROM" filters after the rows are brought to the querying server. Try this instead. I think it should prevent the excess traffic: > > SELECT > s.persnr, > s.vollname, > vt.v_end AS ver_ende, > fa.von AS farzt_datum, > dr.von AS doktr_datum > FROM (select * from stamm where stamm.persnr>0) s > LEFT JOIN vertrag vt ON (vt.persnr = s.persnr AND vt.v_end = > (SELECT max(v_end) FROM vertrag x WHERE x.persnr = s.persnr) ) > LEFT JOIN > ( > SELECT von, persnr FROM verteiler@opserver_tcp:pers_zusatz fa > WHERE attribute = "FARZ" > ) fa > ON (fa.persnr = s.persnr) > LEFT JOIN > ( > SELECT von, persnr FROM verteiler@opserver_tcp:pers_zusatz > WHERE attribute = "PROM" > ) dr > ON (dr.persnr= s.persnr) > ORDER BY s.persnr DESC Hi EEM, you da man! You are right on the spot with your assumption. Your query runs just in under a second! I'm presently rewriting the original query (which is more complicated and, among other things, contains a couple more of these left joins) to see if the performance is still comparable to the OUTER syntax, but now I know I'm on the right track. Thanks a bunch, Richard

Glossary

IDS (Informix Dynamic Server)