How can I fix performance issue in my stored procedure?

  

CREATE PROCEDURE [WMS].[GetSalesOrderSearch]  
 /*  
  EXEC WMS.[GetSalesOrderSearch] @CustomerId=1003,@PageNumber=1,@PageSize=1000,@SearchText='copy230',@SortDescending=0  
  */  
 (  
 @CustomerId INT  
 ,@PageNumber INT  
 ,@PageSize INT  
 ,@SearchText NVARCHAR(MAX) = NULL  
 ,@SortColumn NVARCHAR(255) = 'Id'  
 ,@SortDescending BIT = 0  
 ,@FilterListXml XML = NULL  
 ,@RowCount INT = NULL OUTPUT  
 )  
AS  
BEGIN  
 DECLARE @Query NVARCHAR(MAX) = '';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @SearchJoin NVARCHAR(MAX) = '';  
 DECLARE @FilterQuery NVARCHAR(MAX) = '(SO.CustomerId = @CustomerId AND SO.IsDeleted = 0) ';  
 DROP TABLE  
  
 IF EXISTS #FilterList  
  SELECT CAST(ROW_NUMBER() OVER (  
     ORDER BY n.value('(./AttributeId/text())[1]', 'INT')  
     ) AS VARCHAR(50)) Row  
   ,n.value('(./AttributeId/text())[1]', 'INT') AttributeId  
   ,n.value('(./PropertyName/text())[1]', 'VARCHAR(100)') PropertyName  
   ,n.value('(./AttributeType/text())[1]', 'VARCHAR(100)') AttributeType  
   ,n.value('(./AttributeEntityType/text())[1]', 'VARCHAR(100)') AttributeEntityType  
   ,n.value('(./BeginDate/text())[1]', 'DATETIME') BeginDate  
   ,n.value('(./BeginNumber/text())[1]', 'FLOAT') BeginNumber  
   ,n.value('(./Checked/text())[1]', 'BIT') Checked  
   ,n.value('(./EndDate/text())[1]', 'DATETIME') EndDate  
   ,n.value('(./EndNumber/text())[1]', 'FLOAT') EndNumber  
   ,n.value('(./SearchText/text())[1]', 'NVARCHAR(450)') SearchText  
   ,n.value('(./SearchType/text())[1]', 'NVARCHAR(100)') SearchType  
   ,n.value('(./SelectedOption/text())[1]', 'VARCHAR(450)') SelectedOption  
   ,n.value('(./SelectedOptionList/text())[1]', 'VARCHAR(100)') SelectedOptionList  
  INTO #FilterList  
  FROM @FilterListXml.nodes('/Root/Class/AnonymousType') AS T(n)  
  
 SELECT @FilterQuery += ' AND ( ' + (  
   CASE   
    WHEN AttributeType = 'Text'  
     OR AttributeType = 'Identifier'  
     THEN PropertyName + (  
       CASE SearchType  
        WHEN 'Contains'  
         THEN ' LIKE ''%' + SearchText + '%'''  
        WHEN 'StartsWith'  
         THEN ' LIKE ''' + SearchText + '%'''  
        WHEN 'EndsWith'  
         THEN ' LIKE ''%' + SearchText + ''''  
        WHEN 'Equals'  
         THEN '= ''' + SearchText + ''''  
        ELSE ''  
        END  
       )  
    WHEN AttributeType = 'Number'  
     OR AttributeType = 'Price'  
     THEN (  
       CASE   
        WHEN (  
          BeginNumber IS NOT NULL  
          AND EndNumber IS NOT NULL  
          )  
         THEN PropertyName + ' > ' + CAST(BeginNumber AS VARCHAR(50)) + ' AND ' + PropertyName + ' < ' + CAST(EndNumber AS VARCHAR(50))  
        WHEN BeginNumber IS NOT NULL  
         THEN PropertyName + '> ' + CAST(BeginNumber AS VARCHAR(50))  
        WHEN EndNumber IS NOT NULL  
         THEN PropertyName + '< ' + CAST(EndNumber AS VARCHAR(50))  
        ELSE ''  
        END  
       )  
    WHEN AttributeType = 'Date'  
     THEN (  
       CASE   
        WHEN (  
          BeginDate IS NOT NULL  
          AND EndDate IS NOT NULL  
          )  
         THEN PropertyName + ' > ''' + CAST(BeginDate AS VARCHAR(50)) + '''' + ' AND ' + PropertyName + ' < ''' + CAST(EndDate AS VARCHAR(50)) + ''''  
        WHEN BeginDate IS NOT NULL  
         THEN PropertyName + ' > ''' + CAST(BeginDate AS VARCHAR(50)) + ''''  
        WHEN EndDate IS NOT NULL  
         THEN PropertyName + ' < ''' + CAST(EndDate AS VARCHAR(50)) + ''''  
        ELSE ''  
        END  
       )  
    WHEN AttributeType = 'SimpleSelect'  
     OR PropertyName = 'General_CreatedMemberId'  
     THEN PropertyName + '= ' + '''' + CAST(SelectedOption AS VARCHAR(450)) + ''''  
    WHEN AttributeType = 'SimpleSelect'  
     OR AttributeType = 'Metric'  
     THEN PropertyName + '= ' + CAST(SelectedOption AS VARCHAR(50))  
    WHEN AttributeType = 'MultiSelect'  
     THEN PropertyName + ' IN(' + CAST(SelectedOptionList AS VARCHAR(50)) + ')'  
    WHEN AttributeType = 'YesNo'  
     THEN PropertyName + ' = ' + CAST(Checked AS VARCHAR(50))  
    ELSE ''  
    END  
   ) + ' ) ' + CHAR(13)  
 FROM #FilterList  
  
 IF LEN(ISNULL(@SearchText, '')) > 0  
 BEGIN  
  SET @SearchJoin = '  
  JOIN (   SELECT DISTINCT [KEY],   
    ROW_NUMBER() OVER (PARTITION BY [KEY] ORDER BY CustomRank ASC, RANK DESC) Row  
   FROM  
   ( SELECT [KEY] ,  
     [RANK] ,  
     0 CustomRank  
   FROM   CONTAINSTABLE ( WMS.SalesOrderSearch, (General_OrderId ), ''"*{SearchText}*"'')  
   UNION  
   SELECT [KEY] ,  
     [RANK] ,  
     1 CustomRank  
   FROM   CONTAINSTABLE ( WMS.SalesOrderSearch, (General_UniqueId ), ''"*{SearchText}*"'')  
   UNION  
   SELECT [KEY] ,  
     [RANK] ,  
     2 CustomRank  
   FROM   CONTAINSTABLE ( WMS.SalesOrderSearch, (Contact_Email ), ''"*{SearchText}*"'')  
   UNION  
   SELECT [KEY] ,  
     [RANK] ,  
     3 CustomRank  
   FROM   CONTAINSTABLE ( WMS.SalesOrderSearch, (ProductIdentifers), ''"*{SearchText}*"'')  
   UNION  
   SELECT [KEY] ,  
     [RANK] ,  
     4 CustomRank  
   FROM   CONTAINSTABLE ( WMS.SalesOrderSearch, (AllText), ''"*{SearchText}*"'')  
  ) T  
    ) SOS ON SOS.[KEY] = SO.Id AND SOS.Row = 1  
';  
 END;  
  
 SET @Query = '  
  
SELECT   @RowCount = COUNT(*)  
FROM     WMS.SalesOrderSearch SO WITH(NOLOCK)  
{SearchJoin}  
WHERE   {WhereClause}  
  
SELECT   *  
FROM     WMS.SalesOrderSearch SO WITH(NOLOCK)  
{SearchJoin}  
WHERE   {WhereClause}  
  
ORDER BY SO.{SortColumn} {SortType}  
  
OFFSET (( @PageNumber - 1 ) * @PageSize )   
ROWS FETCH NEXT @PageSize ROWS ONLY  
OPTION ( OPTIMIZE FOR UNKNOWN)';  
 SET @Query = REPLACE(@Query, '{SearchJoin}', @SearchJoin);  
 SET @Query = REPLACE(@Query, '{WhereClause}', @FilterQuery);  
 SET @Query = REPLACE(@Query, '{SearchText}', ISNULL(@SearchText, ''));  
 SET @Query = REPLACE(@Query, '{SortColumn}', ISNULL(@SortColumn, 'Id'));  
 SET @Query = REPLACE(@Query, '{SortType}', CASE @SortDescending  
    WHEN 1  
     THEN 'DESC'  
    ELSE 'ASC'  
    END);  
  
 PRINT @Query;  
  
 EXEC sys.sp_executesql @Query  
  ,N'@CustomerId INT, @PageNumber INT, @PageSize INT,@SortColumn NVARCHAR,@SortDescending BIT,@RowCount INT OUTPUT'  
  ,@CustomerId = @CustomerId  
  ,@PageNumber = @PageNumber  
  ,@PageSize = @PageSize  
  ,@SortColumn = @SortColumn  
  ,@SortDescending = @SortDescending  
  ,@RowCount = @RowCount OUT;  
END;

Select allOpen in new window