Optimizing Stored Procedure Query

Question

See below stored procedure.

It is in use by an app which does searches takes user input for orders and displays them. Most of the time, it returns the expected results in a few seconds. However, when a specific vendor is searched for, it returns the answer in 5-10 seconds. If it includes @vendorsOnly = true, then it is very fast. If @vendorsOnly = 0, then it is slow.

I have tried putting an index on the VendorId, and that seemed to help for a while, but it is running slow again.

I think there is an optimization issue here, but I am not sure what should be done.

ALTER PROCEDURE [dbo].[SearchOrderInfos_v21]
(
    @orderId int = null,
    @customerFileNo nvarchar(max) = null,
    @orderDate Date = null,
    @countyId nvarchar(max) = null,
    @clientName nvarchar(max) = null,
    @orderStatusId int = null,
    @vendorId int = null,
    @vendorETA nvarchar(max) = null,
    @dueDate DateTime = null,
    @guid nvarchar(max) = null,
    @orderStatusCsv nvarchar(max) = null,
    @researcherOrManagerId nvarchar(max) = null,
    @vendorsOnly bit = false,
    @researchersOnly bit = false,
    @includeDeletedRows bit = 0,
    @researcherId nvarchar(max) = null,
    @managerId nvarchar(max) = null,
    @allowViewArchive bit = 0
)
AS
BEGIN
    declare @timeZone varchar(25) = 'Eastern Standard Time' -- bake it in!
    SET NOCOUNT ON
        BEGIN
            Select top (Select ts.MaxRecords from TableSettings ts where ts.TableName = 'Order') 
                O.*,
                C.CountyName, 
                CU.DisplayName AS CustomerName, 
                OS.OrderStatus, 
                SR.Code AS SearchRequestCode, 
                IsNull(V.DisplayName, V.CompanyName) AS Vendor,
                V.EmailAddress, 
                O.CustomerDueDate AS DueDate,
                SP.StateProvinceName as StateProvince,
                U.UserName as Manager,
                M.UserName as Researcher
            FROM [dbo].[Order] O 
            left JOIN [dbo].[County] C ON O.CountyId = C.CountyId
            left JOIN [dbo].[Customer] CU ON O.CustomerId = CU.CustomerId
            left JOIN [dbo].[OrderStatus] OS ON O.OrderStatusId = OS.OrderStatusId
            left JOIN [dbo].[Vendor] V ON O.VendorId = V.VendorId
            left JOIN [dbo].[SearchRequest] SR ON O.SearchRequestId = SR.SearchRequestId
            left JOIN StateProvince SP ON SP.StateProvinceId = O.StateProvinceId
            left join AspNetUsers U on U.Id = O.ManagerId
            left join AspNetUsers M on M.Id = O.ResearcherId

            WHERE
            (@vendorsOnly = 0 or o.VendorId is not null) and
            (@researchersOnly = 0 or O.ResearcherId is not null) and
            (@orderId IS NULL OR O.OrderId  = @orderId) AND
            (@customerFileNo IS NULL OR O.CustomerFileNo LIKE '%' + trim(@customerFileNo) + '%') AND
            (@orderDate IS NULL OR Cast(O.OrderDateTime at time zone @timeZone as Date) = @orderDate) AND
            (@countyId IS NULL OR C.CountyId  = @countyId) AND
            (@clientName IS NULL OR CU.DisplayName LIKE '%' + trim(@clientName) + '%') AND
            (@orderStatusId IS NULL OR OS.OrderStatusId = @orderStatusId) AND
            (@vendorId IS NULL OR V.VendorId = @vendorId) AND
            (@vendorETA IS NULL OR O.VendorETA LIKE '%' + trim(@vendorETA) + '%') AND
            (@dueDate IS NULL OR O.CustomerDueDate = @dueDate) and
            (@guid IS NULL OR O.Guid = @guid) and
            (   --If @researcherOrManagerId is not null, then ensure that the order is assigned to the id as Manager or Researcher
                @researcherOrManagerId IS NULL or 
                (
                    O.ResearcherId = @researcherOrManagerId or 
                    O.ManagerId = @researcherOrManagerId or 
                    -- if @allowViewArchive is true, then allow to view if the Order in a status of Client Delivered.
                    (@allowViewArchive = 1 and O.OrderStatusId = 4) ) 
                
            ) and
            (@researcherId is null or O.ResearcherId = @researcherId) and
            (@managerId is null or O.ManagerId = @managerId) and
            (@orderStatusCsv IS NULL or O.OrderStatusId in (Select value from STRING_SPLIT(@orderStatusCsv, ','))) and
            (@includeDeletedRows = 1 or O.DeletedDate is null)
    
            --order by OrderId -- For testing only       
            ORDER BY OS.SortOrder, O.OrderId DESC
        end
END

Answer 1

Like mentioned in the comments, the easiest method to "fix" this would be to add OPTION (RECOMPILE) at the end of your statement, in this case this would go after the ORDER BY:

...
            ORDER BY OS.SortOrder, O.OrderId DESC
            OPTION (RECOMPILE);
        END
END

As also, however, mentioned, if this SP is being run frequently, or generating the plan is costly itself, then using a dynamic approach is better, but is a much more complex solution. Taking your query, I believe the below would be the correct implementation, but I cannot test this. You will need to use your "best friend" to help you with that:

ALTER PROCEDURE [dbo].[SearchOrderInfos_v21] (@orderId int = NULL,
                                              @customerFileNo nvarchar(MAX) = NULL,
                                              @orderDate date = NULL,
                                              @countyId nvarchar(MAX) = NULL,
                                              @clientName nvarchar(MAX) = NULL,
                                              @orderStatusId int = NULL,
                                              @vendorId int = NULL,
                                              @vendorETA nvarchar(MAX) = NULL,
                                              @dueDate datetime = NULL,
                                              @guid nvarchar(MAX) = NULL,
                                              @orderStatusCsv nvarchar(MAX) = NULL,
                                              @researcherOrManagerId nvarchar(MAX) = NULL,
                                              @vendorsOnly bit = false,
                                              @researchersOnly bit = false,
                                              @includeDeletedRows bit = 0,
                                              @researcherId nvarchar(MAX) = NULL,
                                              @managerId nvarchar(MAX) = NULL,
                                              @allowViewArchive bit = 0)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @timeZone varchar(25) = 'Eastern Standard Time'; -- bake it in!

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'SELECT TOP (SELECT ts.MaxRecords FROM TableSettings ts WHERE ts.TableName = ''Order'')' + @CRLF +
               N'       O.*,' + @CRLF +
               N'       C.CountyName,' + @CRLF +
               N'       CU.DisplayName AS CustomerName,' + @CRLF +
               N'       OS.OrderStatus,' + @CRLF +
               N'       SR.Code AS SearchRequestCode,' + @CRLF +
               N'       ISNULL(V.DisplayName, V.CompanyName) AS Vendor,' + @CRLF +
               N'       V.EmailAddress,' + @CRLF +
               N'       O.CustomerDueDate AS DueDate,' + @CRLF +
               N'       SP.StateProvinceName AS StateProvince,' + @CRLF +
               N'       U.UserName AS Manager,' + @CRLF +
               N'       M.UserName AS Researcher' + @CRLF +
               N'FROM [dbo].[Order] O' + @CRLF +
               N'     LEFT JOIN [dbo].[County] C ON O.CountyId = C.CountyId' + @CRLF +
               N'     LEFT JOIN [dbo].[Customer] CU ON O.CustomerId = CU.CustomerId' + @CRLF +
               N'     LEFT JOIN [dbo].[OrderStatus] OS ON O.OrderStatusId = OS.OrderStatusId' + @CRLF +
               N'     LEFT JOIN [dbo].[Vendor] V ON O.VendorId = V.VendorId' + @CRLF +
               N'     LEFT JOIN [dbo].[SearchRequest] SR ON O.SearchRequestId = SR.SearchRequestId' + @CRLF +
               N'     LEFT JOIN StateProvince SP ON SP.StateProvinceId = O.StateProvinceId' + @CRLF +
               N'     LEFT JOIN AspNetUsers U ON U.Id = O.ManagerId' + @CRLF +
               N'     LEFT JOIN AspNetUsers M ON M.Id = O.ResearcherId' + @CRLF +
               N'WHERE O.OrderStatusId = 4' + @CRLF + --This appears to be the only clause that doesn't reference a parameter, so putting it at the start as this makes things far easier
               CONCAT(CASE WHEN @vendorsOnly = 1 THEN N'  AND O.VendorId IS NOT NULL' + @CRLF END,
                      CASE WHEN @researchersOnly = 1 THEN N'  AND O.ResearcherId IS NOT NULL' + @CRLF END,
                      CASE WHEN @orderId IS NOT NULL THEN N'  AND O.OrderId = @orderId' + @CRLF END,
                      CASE WHEN @customerFileNo IS NOT NULL THEN N'  AND O.CustomerFileNo LIKE ''%'' + trim(@customerFileNo) + ''%''' + @CRLF END,
                      CASE WHEN @orderDate IS NOT NULL THEN N'  AND CAST(O.OrderDateTime AT TIME ZONE @timeZone AS Date) = @orderDate' + @CRLF END, --Warning, this isn't going to be SARGable,
                                                                                                                                                     --you should do use AT TIME ZONE on @orderDate, not O.OrderDateTime
                      CASE WHEN @countyId IS NOT NULL THEN N'  AND C.CountyId = @countyId' + @CRLF END,
                      CASE WHEN @clientName IS NOT NULL THEN N'  AND CU.DisplayName LIKE ''%'' + TRIM(@clientName) + ''%''' + @CRLF END,
                      CASE WHEN @orderStatusId IS NOT NULL THEN N'  AND OS.OrderStatusId = @orderStatusId' + @CRLF END,
                      CASE WHEN @vendorId IS NOT NULL THEN N'  AND V.VendorId = @vendorId' + @CRLF END,
                      CASE WHEN @vendorETA IS NOT NULL THEN N'  AND O.VendorETA LIKE ''%'' + TRIM(@vendorETA) + ''%''' + @CRLF END,
                      CASE WHEN @dueDate IS NOT NULL THEN N'  AND O.CustomerDueDate = @dueDate' + @CRLF END,
                      CASE WHEN @guid IS NOT NULL THEN N'  AND O.Guid = @guid' + @CRLF END,
                      CASE WHEN @researcherOrManagerId IS NOT NULL AND @allowViewArchive = 0 THEN N'  AND (O.ResearcherId = @researcherOrManagerId OR O.ManagerId = @researcherOrManagerId)' + @CRLF END, --I think the WHEN is correct
                      CASE WHEN @researcherId IS NOT NULL THEN N'  AND O.ResearcherId = @researcherId' + @CRLF END,
                      CASE WHEN @managerId IS NOT NULL THEN N'  AND O.ManagerId = @managerId' + @CRLF END,
                      CASE WHEN @orderStatusCsv IS NOT NULL THEN N'  AND O.OrderStatusId IN (SELECT value FROM STRING_SPLIT(@orderStatusCsv, '',''))' + @CRLF END,
                      CASE WHEN @includeDeletedRows = 0 THEN N'  AND O.DeletedDate IS NULL' + @CRLF END) +
              N'ORDER BY OS.SortOrder,' + @CRLF +
              N'         O.OrderId DESC;';

    --PRINT @SQL; --Your debugging best friend

    DECLARE @Params nvarchar(MAX) = N'@orderId int,
                                      @customerFileNo nvarchar(MAX),
                                      @orderDate date,
                                      @countyId nvarchar(MAX),
                                      @clientName nvarchar(MAX),
                                      @orderStatusId int,
                                      @vendorId int,
                                      @vendorETA nvarchar(MAX),
                                      @dueDate datetime,
                                      @guid nvarchar(MAX),
                                      @orderStatusCsv nvarchar(MAX),
                                      @researcherOrManagerId nvarchar(MAX),
                                      @vendorsOnly bit,
                                      @researchersOnly bit,
                                      @includeDeletedRows bit,
                                      @researcherId nvarchar(MAX),
                                      @managerId nvarchar(MAX),
                                      @allowViewArchive bit';
    EXEC sys.sp_executesql @SQL,
                           @Params, 
                           @orderId,
                           @customerFileNo,
                           @orderDate,
                           @countyId,
                           @clientName,
                           @orderStatusId,
                           @vendorId,
                           @vendorETA,
                           @dueDate,
                           @guid,
                           @orderStatusCsv,
                           @researcherOrManagerId,
                           @vendorsOnly,
                           @researchersOnly,
                           @includeDeletedRows,
                           @researcherId,
                           @managerId,
                           @allowViewArchive;
                           
END;