RE: Interesting one

thx. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: Art S. Kagel (Oninit) [mailto:art@oninit.com] Sent: Tuesday, March 18, 2008 10:30 AM To: Jack Parker Cc: Informix-List@Iiug. Org Subject: 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? > One problem I see is that the filter on column2 in the original query are in the WHERE clause and not in the ON clause. ON clause filters are applied pre-join and WHERE clause filters are applied post-join according to the new ANSI SQL rules. That means that the optimizer has to essentially perform the join without the WHERE filters to a temp table, then select from the temp table filtered by the WHERE clause. This is more expensive and does require the creation of the temp table which could definitely be blocked by the onmode -c. Art S. Kagel Oninit > 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. > > ============================================================================ =============== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ============================================================================ ===============