How can I forceseek?
CREATE INDEX IX_dbo_listings__BrandId_Category_Status ON dbo.listings (BrandId, Category, [Status]);
…then rewrite the stored procedure to avoid the scans and sort:
ALTER PROCEDURE [dbo].[usp_ListingSimilarProducts] @Id integer = NULL AS BEGIN SET NOCOUNT ON; -- BrandId to Ordering mapping CREATE TABLE #Map ( BrandId integer, OrderColumn integer, PRIMARY KEY CLUSTERED (OrderColumn) ); -- Static mapping values from the CASE expressions INSERT #Map (BrandId, OrderColumn) VALUES (245, 1), (52, 2), (213, 3), (40, 4), (141, 5), (100, 6), (232, 7), (142, 8), (99, 9), (24, 10), (200, 11), (96, 12), (57, 13), (404, 14), (0, 99); DECLARE @BId integer; DECLARE @CId integer; -- Two assignments in one query SELECT @BId = L.BrandId, @CId = L.Category FROM dbo.listings AS L WHERE L.Id = @Id; -- Add the extra mapping if @BId <> 0 IF @BId <> 0 INSERT #Map (BrandId, OrderColumn) VALUES (@BId, 0); -- Results SELECT TOP (5) L.*, U.Username FROM #Map AS M JOIN dbo.listings AS L ON L.BrandId = M.BrandId JOIN dbo.users AS U ON U.Id = L.[User_id] WHERE L.[Status] = 1 AND L.Category = @CId AND U.IsLockedOut = 0 ORDER BY M.OrderColumn ASC; END;
The estimated execution plan is:
The important part is the final query:
The order provided by the clustered index on the mapping table is preserved, so no sorting is needed. The only table scanned is the very small mapping table itself. The Key Lookups are optimal here because there are only likely to be a very small number (probably five, depending on how often users are locked out) so it makes more sense to do a limited number of lookups rather than include all the necessary columns in the new index.
Oh, and you're still on SQL Server 2012 RTM. You should apply at least Service Pack 2.