Re: SQL renamed expression name not recognised in WHERE
- From: "DBDriver" <DBDriver@xxxxxxxx>
- Date: Sun, 16 Jul 2006 04:21:29 GMT
"Jonathan Ball" <jonball@xxxxxxxxxxxxxx> wrote in message
news:RNwIc.58$mL5.30@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
DBDriver wrote:
I'm not sure of a performance issue. I ran both the
OP's original query and my nested table expression
suggestion through VisualExplain in iSeries Navigator,
and the access path used and the method to determine it
were identical. I'll have to try it later today in a
better setting than what I can do from home.
It also depends a lot on the DB involved. By way of example, we encountered
the following recently...
SELECT Xa, Xb, Xc, SUM(Xd)
FROM TableX
GROUP BY Xa, Xb, Xc
HAVING (Xa = Value1) AND (Xb = Value2) AND (Xc = Value3)
Now this query should flounder on a large database if it treats the HAVING
directive as a filter on a derived value only. Reading this literally, the
result set is formed and then the derived "grouped" results are calculated
before they are filtered by the Having statement.
Under SQL Server 2000 this worked perfectly fine (no performance hit).
Under Oracle 9i it nose dived as the table size grew.
We didn't test this under any DB2 variants as it wasn't a supported DB of
the project.
The corrected version (which run equally well under SQL Server and Oracle
9i) was to force the filtering at a lower level (the original resultset).
i.e..
SELECT Xa, Xb, Xc, SUM(Xd)
FROM TableX
WHERE (Xa = Value1) AND (Xb = Value2) AND (Xc = Value3)
GROUP BY Xa, Xb, Xc
The best we can gauge is that the SQL Server was more sensitive to this type
of programming error and correctly applied the "Having" condition back at a
lower level as a record filter. Oracle 9i on the other hand was very literal
in it's interpretation. It should also be noted that on relatively small
tables Oracle ran the original form very quickly!
I am not sure why your DB2 tests didn't reveal any performance discrepancy
but it could indeed be due to the complexity of the where condition
impacting on record selection. You could find, though, that a different
database will not behave in a similar fashion. I know for sure that, in the
case of the subquery example, SQL Server would run this type of query/filter
extremely slowly by comparison to the single select statement.
RJ.
.
- References:
- Re: SQL renamed expression name not recognised in WHERE
- From: Jonathan Ball
- Re: SQL renamed expression name not recognised in WHERE
- Prev by Date: Re: Lexmark 2400 printer creeps up when printing.
- Next by Date: Re: Question about placing service programs in a BNDDIR.
- Previous by thread: Re: SQL renamed expression name not recognised in WHERE
- Next by thread: Re: Lexmark 2400 printer creeps up when printing.
- Index(es):
Relevant Pages
|
