Re: Interesting one

Jack Parker wrote: > Ran into a curious one Friday. A series of ER replicants are set up which > can post changes back. To ensure that no such activity is happening when a > master process starts, it blocks (onmode -c) the primary while it reads the > vital configuration info - once up, changes to that info are communicated to > the process, but for that brief moment, it needs absolute quiet. > > Problem arose in a query which used an IN statement, the sort of thing we've > seen out of SQLServer for a while: > > select column from a as tab1 inner join b as tab2 > on tab1.keycol = tab2.keycol > where column2 IN (more of this nonsense)> > The query hangs when running, you can check and onmode -g con and see it > hung up. Re-writing the query into the older SQL92 syntax: > > select column > from a, b, c, d > where a.keycol1=b.keycol1 > and b.keycol2=c.keycol2 and so forth> > works fine. > > My thought is that the first query requires some resource that it cannot > have while the engine is blocked, either through the "IN" - which might > perhaps require a temp table, or through the INNER JOIN - god only knows how > that is implemented. > > Thoughts? > > BTW, query came out +20% cheaper running using the older syntax, mostly > because with the newer syntax it used index joins strictly - even though one > of tables was 1/2 a page. Older syntax used a sequential scan on the dinky > table and came out at a cost of 7 vs 9. > > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > Spot on! ANSI joins are way more expensive than non ANSI because the WHERE clause is applied as a _post_ join filter. Ie Chances are that first the join is materialized in a temporary file (which would explain why the query halts on the onmode -c block), and then that is scanned for the conditions in the WHERE clause, while old style joins would apply join filters and any other conditions in the WHERE clause all in one go. The alternative is to move extra conditions in the WHERE clause to the join, eg select column from a as tab1 inner join b as tab2 on tab1.keycol = tab2.keycol and tab2.column2 IN (.....) which should take the same path as the old style join -- 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