John,

> What are the actual times taken?

I had to remove some unimportant statements from the stored procedure:
the clear time just for this statement is 1.54 sec. (this is not much, I
know, but on the slow machines of my client it can take 5 times longer.

> What is the rowcount of the resultset?

70.000 records in the table untersuchungen, 51 in the resulting dataset.

> If you change the left outer join to just join does it make any difference?

Time is 1.21 sec. then, but I don't think an inner join is possible here
because there can be null values on the right side.

> Just as an aside, and it will probably run slower than the first query, you
> can use Tim's very excellent correlated subquery to get the result without
> an explicit join

I cancelled the execution after 5 minutes.

Thanks Uli

Uli


>Time is 1.21 sec. then, but I don't think an inner join is possible here
>because there can be null values on the right side.

I think inner join must be OK as (if) the SQL Condition for selecting the
count distinct is the same as the SQL Condition for the other fields.

> I cancelled the execution after 5 minutes.

Hehe.  I though it might be quite nasty.

John

Ulrich,

<< I know: it is just theoretical, but it would be interesing to know why.
>>

Most of the time this type of difference can simply be chalked up to the
difference between querying a static temporary set of data that is private
to your session vs. querying a live table.  The latter has to performance
locking and change detection that the former does not.

I can't say for sure that this is entirely the case here without examining
the the script and SQL in great detail, but it is usually the difference.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> I can't say for sure that this is entirely the case here without examining
> the the script and SQL in great detail, but it is usually the difference.

Interesting - Thanks!

Uli