How can I forceseek?

systemr 2015-07-23 13:59:19

I would love to take someones input on how I can basically force this stored procedure to make an index seek rather than an index scan on my PK..any comments would be great.

/storage/attachments/1310-Plan.pesession
Aaron Bertrand 2015-07-23 18:09:13
Do you really need SELECT *? That's a lot of columns. What is the definition of the PK? Have you considered a covering filtered index, leading with the Category column, WHERE status = 1?
SQLkiwi 2015-07-24 02:23:07
I would create this index:

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:

Estimated procedure plan

The important part is the final query:

Main 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.

July 23, 2015
No Comments