I would like to ask for some help to create correct indexes for this query:
SELECT DISTINCT D.TypeID FROM dbo.Sizes AS S WHERE EXISTS ( SELECT 1 FROM #PermissionsTable AS PT WHERE PT.ProductID = S.ProductID AND PT.CountryID = S.CountryID );
This query is used in procedure and
#PermissionsTable is created in there based on passed in criteria.
I've tried creating various indexes on my table, but I'm always getting only Index Scans, I'd like to get Seeks of course. For instance:
CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID_TypeID ON dbo.Sizes (ProductID, CountryID, TypeID); CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID_TypeID ON dbo.Sizes (CountryID, ProductID, TypeID); -- I've added TypeID into INCLUDE part, because I'm not using it in any clause except SELECT statement. CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID ON dbo.Sizes (ProductID, CountryID) INCLUDE(TypeID); CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID ON dbo.Sizes (CountryID, ProductID) INCLUDE(TypeID);
#PermissionTable I've tried creating both
Non Clustered indexes on
ProductID, CountryID or
But I'm always ending up with Scans.
Sizes table has hundreds of millions of rows. Permissions table has around 400.000.
At the moment I've added
WITH (FORCESEEK) hint next to
FROM dbo.Sizes AS S, which forces seek, but I'd like this to be done by SQL Server engine.
Any literatur, tips, anything would be helpful.
Update: added execution plan