1

Topic: Distinct in a subquery

Hello dear experts! Help  to understand why 2 requests differ on runtime on the order

1.
select t. B, tr.id
from (select distinct Ref, B
from t
) t
inner join tr on tr. Ref = t. Ref
2.
select distinct B, Ref
into #t
from t
select t. B, tr.id
from #t t
inner join tr on tr. Ref = t. Ref

In the table tr - 3000 records (all unique)
In the table t - 2070 records (5 unique)
1st is fulfilled some minutes
2nd is fulfilled 10

2

Re: Distinct in a subquery

I apologize
Both tables t and tr -

3

Re: Distinct in a subquery

Sandist wrote:

why 2 requests differ on runtime on the order

execution plans different certainly...

4

Re: Distinct in a subquery

The grandfather;
All is true, thanks
Used inner hash join and all fell into place (I understand risk and I know that in case the request produces not 5 unique, and 5000 all changes also the help can be wrong... It is single request)