Posted by Thomas R. Hummel on 07/14/06 16:55

Alexander Kuznetsov wrote:
> Tom,
>
> you might want to sort and aggregate a narrow result set first, then
> join:
>
> SELECT M.my_id, M.line_number,
> code1,
> code2,
> code3,
> code4
> FROM Main_Table M
> LEFT JOIN (
> select my_id,
> MIN(CASE R.seq_no WHEN 1 THEN R.my_code END) code1,
> MIN(CASE R.seq_no WHEN 2 THEN R.my_code END) code2,
> MIN(CASE R.seq_no WHEN 3 THEN R.my_code END) code3,
> MIN(CASE R.seq_no WHEN 4 THEN R.my_code END) code4
> from Related_Table r
> group by my_id
> ) R
> ON M.my_id = R.my_id
>
>
> In many cases it is dramatically faster. We were describing it in
> article named "The Less SQL Server Sorts, the Faster It Responds".

Thanks for the suggestion. When I checked a solution based on this the
estimated cost in the query plan was indeed lower and it ran in just
over 10m vs. the 12m45s of the multiple join solution. I may end up
sticking with the multiple join solution because I think that it's more
obvious to future coders what the code is trying to do and the
performance difference is not a big factor, but I'll definitely put
this solution in my toolbox for future use.

Thanks!
-Tom.

[Back to original message]