September 11, 2009 at 6:19 am
The purpose of below query is to get the
DISTINCT name from myTable and it should not be in myAnotherTable for productCode = 'SomeProduct'
Select distinct name from myTable
where name NOT IN
(select productName from myAnotherTable
where productCode = 'SomeProduct')
order by 1
myTable has some 300,000 rows
select DISTINCT name from myTable alone gives 238 rows(with one NULL entry)
subquery returns - 13 rows
Is there any way to improve the performance of this query?
-
KB
Thanks,
Santhosh
September 11, 2009 at 6:28 am
Except might be better
Select name from myTable
except
select productName from myAnotherTable
where productCode = 'SomeProduct'
September 11, 2009 at 6:59 am
Did you try EXISTS?
Select distinct name
from myTable
where NOT EXISTS (
select 1
from myAnotherTable
where productCode = 'SomeProduct'
AND productName = myTable.Name
)
order by 1
-- Gianluca Sartori
September 11, 2009 at 7:02 am
Select name from myTableexcept
select productName from myAnotherTable
where productCode = 'SomeProduct'
Gianluca Sartori (9/11/2009)
Did you try EXISTS?
Select distinct name
from myTable
where NOT EXISTS (
select 1
from myAnotherTable
where productCode = 'SomeProduct'
AND productName = myTable.Name
)
order by 1
Yes, I tried both, seems both are running faster.
I do not have permissions to see the execution plan. But better than NOT IN.
-
KB
Thanks,
Santhosh
September 11, 2009 at 7:15 am
KB (9/11/2009)
Yes, I tried both, seems both are running faster.
I do not have permissions to see the execution plan. But better than NOT IN.
Glad of that.
TBH , i didnt know that permissions to view the plan can be restricted.
I would certainly argue that its essential to see the plan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply