How can I fix performance issue in my stored procedure?

programmerist
programmerist used Ask the Experts™
on
Hi;

How can I improve performance issue ? I created below stored procedure but if I check costly stored procedure by using sql query to detect them. I realized that below query needs improvement to fix performance issue.Can you help me please?

  

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;

Open in new window



*I attached my execution plan below

ExecutionPlan1.sqlplan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Partha MandayamTechnical Director

Commented:
You can optimize it using SQL Profiler. It will tell you how much time each query takes and you can identify missing indexes, etc which can speed up your query.
ste5anSenior Developer

Commented:
The first step is the most important step, the hard one, and often missed: How do you measure performance? How do you define the goal of improvement needed?

Then you can simply execute your procedure and look at the actual query plan and use SET STATISTICS IO ON; to get some numbers.

But the actual procedure has raises some architectural questions:

1. Cache values... the result for returning the total number of matching rows. Don't execute it always, when you change the page. The filter query itself must also not be run every time.
2. Instead of running a UNION when you need only one row (key/rank) should be separated into single steps. Just execute the next query, when the previous does not return anything.
3. Paging on the one side, but returning * sounds weird. Do you really need all columns?
4. Look at the possible WHERE conditions, do indices exist for each case?
5. You have already a FULLTEXT index, but you're using plain LIKE searches (contains, endswith), where indices may not be usable.
6. When parsing XML use always the concrete path. Using ./ is slower.

I would start by changing the overall approach:

1. Run a "prepare" from your application.
2. Execute the "query" to return the paged data only.

Here I would look into sp_prepare first. Otherwise you need to your own caching and "preparing".

Author

Commented:
Thank you for your great help. But I realized that Execution plan extremely huge to attach here. Can you refactor my code for best performance.
Thank you.
ste5anSenior Developer

Commented:
Just post the .sqlplan file, use the Save Execution Plan As.. item in the context menu.

And for your request: It's not really possible to do this. Searching for best performance is in many scenarios a trial and error approach in conjunction with looking into what happens in your system.
Especially as you have in my opinion an serious architectural error which affects also the consuming front-end.

Author

Commented:
Hi ste5an;

I am attaching my execution plan here. Can you check this out ? Thank you.

KR
Yusuf
ExecutionPlan1.sqlplan

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial