CODEWITHSUNDEEP
sql-serverstored-proceduresquery-performance
snadell
snadell

Reputation: 33

SQL Server - Performance of query while using IN operator when data is huge

I have a query which takes in VendorId as the parameter and it should return all the electronic products of that vendor which are in 'Approved' state and also have a valid registry entry.

The problem is many products have RegistryEntry to check if that RegistryEntry of that product is valid i need to check in tblRegistries table with StatusId=2 which means it is a valid key.

The below query works fine, my concern in regarding the performance by using

IN (SELECT RegistryEntry FROM tblRegistries WHERE StatusId = 2)

because in prod environment there is lot of data in tblRegistries table and when i say

SELECT RegistryEntry FROM tblRegistries WHERE StatusId = 2

it returns hundreds of thousands of records

SELECT * FROM [dbo].[Product] P
INNER JOIN [dbo].[ElectronicProduct] EP
ON P.ProductId = EP.ProductId
WHERE P.VendorId = @VendorId
AND P.[RegistryEntry] IN (SELECT RegistryEntry FROM tblRegistries WHERE StatusId = 2) AND EP.Status = 'Approved'

I am looking for some expert suggestions on this if this can be done in a better way using joins or other features.

Upvotes: 0

Views: 113

Answers (2)

Northbank75
Northbank75

Reputation: 46

I find exists runs a lot quicker often ...

SELECT * FROM [dbo].[Product] P
INNER JOIN [dbo].[ElectronicProduct] EP
ON P.ProductId = EP.ProductId
WHERE P.VendorId = @VendorId
AND EXISTS (
   SELECT RegistryEntry FROM tblRegistries 
   WHERE StatusId = 2 and RegistryEntry = P.[RegistryEntry])

Upvotes: 0

Acuao
Acuao

Reputation: 691

Did you tryed something like this ?

SELECT * FROM [dbo].[Product] P
INNER JOIN [dbo].[ElectronicProduct] EP ON P.ProductId = EP.ProductId
INNER JOIN tblRegistries R ON R.RegistryEntry = P.RegistryEntry AND StatusId = 2
WHERE   P.VendorId = @VendorId
        AND EP.Status = 'Approved'

Also adding index on joined fields should improve performance

Upvotes: 1

Related Questions