September 13, 2013 at 7:03 pm
I have 2 tables(label and product) both has the column called uniqueCode which is nvarchar(255) datatype and nullable column.
product table has 30,000 records and label table has 5000 records. I would like to get the uniqueCode from label which doesn't exist in the product table upc column. I tried with not in but returns null data but i have 3000 unmatched data that needs to be displayed. Then i tried with join and it is taking 10+ minutes to provide the result.
select * from label where not in(select uniqueCode from Product where uniqueCode is not null) and uniqueCode is not null
SELECT pp.*
FROM label pp
LEFT JOIN Product p ON pp.uniqueCode <> p.uniqueCode
WHERE
p.uniqueCode is not NULL
[Note : both the columns are non indexed column]
Am i missing anything in this query ? why it is taking too much time to execute. Any suggestions....
September 14, 2013 at 12:29 am
Those two queries are completely different, and the first has a syntax error to boot.
First the syntax error
select * from label
where label.uniqueCode not in (select uniqueCode from Product where uniqueCode is not null) and label.uniqueCode is not null
The join version to that would be
SELECT pp.*
FROM label pp
LEFT JOIN Product p ON pp.uniqueCode = p.uniqueCode AND p.uniqueCode IS NOT NULL
WHERE
p.uniqueCode is NULL AND pp.UniqueCode IS NOT NULL
NOT EXISTS will probably be faster than both
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2013 at 7:29 am
Thank you Gila.
i am done with my requirement using below three concepts.
1. using Not In
2. Using Left Join
3.Using Not Exists.
Which one is the best choice to achieve this ? suggestions please... but all are taking 4 seconds to produce the result.
as i said in my earlier post label table has 5000 records and product table has 30000 records. Is it fine to take 4 seconds to search and produce the result?
September 14, 2013 at 8:06 am
born2achieve (9/14/2013)
i am done with my requirement using below three concepts.1. using Not In
2. Using Left Join
3.Using Not Exists.
Which one is the best choice to achieve this ?
Did you read the blog posts I referenced?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2013 at 11:55 am
Opps!! i didn't see the link you posted. Now i read the blog and finally for my requirement i am opt to use Not Exists as i have Null in matching column.
Thanks for your time. But the only concern is why all three concept takes the same time. As per the blog Not Exists will take less time than Not in and left outer join. But in my case all three concepts takes the same time to produce the result
September 15, 2013 at 4:45 pm
You've got a small row count, so differences in time probably won't be noticable and if you look at the examples in the blog posts, you'll see that mostly the time differences are small, so if you're just using SSMS's query duration you probably won't see any differences
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2013 at 5:40 pm
Agreed and thanks for your response
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply