quote:

---

Originally posted by Dargon

Hi, All,

I feel confused about the query performance and can not explain why I am getting such a strange result. My query is pretty simple:


select Event.Qty from Event
where Event.Uom in (Select UoM from UoM where exp_Mass=1)

Here Event table is really huge one - about 9000000 records, UoM is small lookup table. So execution time is more then 1 min. From what I knew so far, it seems that inner join is much more prefferable way to do subset of records comparing to IN ox EXISTS clauses. So I changed query like this:

select Event.Qty from Event
inner join UoM on UoM.UoM=Event.UoM
where UoM.exp_Mass=1

Do you index on these?

Sounds obvious, right? But it actually runs SLOWER, then original one and I am really puzzled why. The Event table is indexed by another column(EventID), which is not included into query, and subquery in the first example returns small set of data, but the result of outer SELECT is huge as well: about 4.5 million records.

So does anybody know why it happens?

Thanks,
Dargon

---

quote:

---

Originally posted by Dargon

IN case: Event table clustered Index scan - 75%
Hash match(right semi join) - 24%
Parallelism(Cather streams): 0%

Inner join case:
Event table clustered Index scan - 70%
Hash match(inner join): 23%
Parallelism(Cather streams): 7%

Can you use :
Option (maxdop 1) in your query

So you see that the final operation takes longer in the JOIN case. I assume that Index scanning is the same in both cases.

---