CODEWITHSUNDEEP
sql-servert-sql
Larry Grady
Larry Grady

Reputation: 519

More efficient way to avoid dupes

I am doing a search engine that receives a list of Supplier and Brand ID's. They come in as strings and are converted into a Table with a UDF. Every Brand has a Supplier ID.

So in order to limit my search by the brand and suppliers that are selected by the user I need to fill temporary tables with these values and then JOIN them to my search results. Now the user may choose a whole supplier to search but then later choose brands from another differnet supplier. This has to be an order scenario, so in addition to filling the Supplier List iwth the supplier ID's passed into the parameter, I also have to grab the Supplier ID's from the brands they pass in and load them also.

There is a bunch of authorization code also that i've stripped out but this is basically what I'm doing. 

INSERT INTO #TempBrandList (Brand_ID, Brand_Owner) 
SELECT gID, brands.brandowner FROM dbo.f_ConvertGuidList_to_Table(@brandList) JOIN brands ON TempBrandList.Brand_ID=brands.brandid

INSERT INTO #TempSupplierList (Supplier_ID)
SELECT gID FROM dbo.f_ConvertGuidList_to_Table(@supplierList)

INSERT INTO #TempSupplierList (Supplier_ID)
SELECT DISTINCT Brand_Owner FROM #TempBrandList WHERE Brand_OWNER NOT IN (SELECT Supplier_ID FROM #TempSupplierList)

I'm worried that the NOT IN is going to cause problems and isn't very efficient. Would it be better to have 2 #temp tables, just do a straight insert into it and then do another DISTINCT insert into the second one, joining the second one to my main search code to limit results?

Upvotes: 1

Views: 78

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

For the last statement I would use NOT EXISTS instead of NOT IN thus:

INSERT INTO #TempSupplierList (Supplier_ID)
SELECT DISTINCT x.Brand_Owner 
FROM #TempBrandList x
WHERE NOT EXISTS 
(
    SELECT * FROM #TempSupplierList y 
    WHERE x.Brand_OWNER = y.Supplier_ID
)

Why ?

1) NOT IN could give you unexpected results:

SELECT 1 WHERE 2 NOT IN(SELECT 3 UNION ALL SELECT NULL)

vs.

SELECT 1 WHERE NOT EXISTS( SELECT * FROM (SELECT 3 UNION ALL SELECT NULL) x(y) WHERE x.y = 2 )

Results:

-----------

(0 row(s) affected)


-----------
1

(1 row(s) affected)

2) For performance reason. Please read this article.

Note: you should run some tests to see yourself what is the best option for you.

Upvotes: 3

Related Questions