Posted by Roy Harvey on 02/19/07 23:15

On 19 Feb 2007 15:01:46 -0800, bbla32@op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@snet.net> wrote:
>> as an EXISTS test. In any case the use of subqueries in the CASE
>> should limit the number of times the subqueries are executed, which is
>> where any performance gain will come from.
>
>Well, wouldn't it execute slower since the subquery is within CASE?

No reason why that should be an issue.

The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.

Roy Harvey
Beacon Falls, CT

[Back to original message]