Offer to increase performance in stored procedure

Question

I write this query in SQL Server 2016:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[s2_GetReceivedDocumentsCount] 
    @_Username NVARCHAR(MAX),
    @_SearchText NVARCHAR(MAX),
    @_States NVARCHAR(MAX),
    @_Senders NVARCHAR(MAX),
    @_Date1 NVARCHAR(MAX),
    @_Date2 NVARCHAR(MAX),
    @_Filter1ID NVARCHAR(MAX),
    @_Filter1Value NVARCHAR(MAX)
AS
BEGIN   
    --https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
    DECLARE @Username NVARCHAR(MAX)
    DECLARE @Fild BIT
    DECLARE @SearchText NVARCHAR(MAX)
    DECLARE @States NVARCHAR(MAX)
    DECLARE @Senders NVARCHAR(MAX)
    DECLARE @Date1 NVARCHAR(MAX)
    DECLARE @Date2 NVARCHAR(MAX)
    DECLARE @Filter1ID NVARCHAR(MAX)
    DECLARE @Filter1Value NVARCHAR(MAX)

    SELECT
        @Username = @_Username,
        @SearchText = @_SearchText,
        @States = @_States,
        @Senders = @_Senders,
        @Date1 = @_Date1,
        @Date2 = @_Date2,
        @Filter1ID = @_Filter1ID,
        @Filter1Value = @_Filter1Value

    SELECT @SearchText = LTRIM(RTRIM(IsNull(@SearchText, '')))
    SELECT @Filter1ID = LTRIM(RTRIM(IsNull(@Filter1ID, '')))
    SELECT @Filter1Value = LTRIM(RTRIM(IsNull(@Filter1Value, '')))

    DECLARE @PersonalPostID INT = NULL

    SELECT @PersonalPostID = p.PostID
    FROM Person pr
    JOIN PersonPost pp ON pp.PersonID = pr.PersonID
    JOIN Post p ON p.PostID = pp.PostID
    WHERE pr.Username = @Username       
      AND p.IsPersonalPost = 1

    DECLARE @independentPostExists BIT = CASE 
                                            WHEN EXISTS (SELECT 1 FROM Post t 
                                                         WHERE t.Independent = 1 AND t.PostID > 0) 
                                               THEN 1 
                                               ELSE 0 
                                         END

    DECLARE @temp0 TABLE (
        DocumentID int  
        , Likeness int  
        , ParentScrutinyID int
    )   

    ;With AllPost
    As
    (
        Select pp.PostID
        From
            PersonPost pp
            Join Person prs On prs.PersonID = pp.PersonID
        Where prs.Username = @Username
        Union
        Select [type].PostID
        From
            Post [type]
            Join Post p On p.TypeID = [type].PostID
            Join PersonPost pp On pp.PostID = p.PostID
            Join Person prs On prs.PersonID = pp.PersonID
        Where prs.Username = @Username
)
    ,
     SplitSearchText
    AS
    (
        Select * From dbo._1001_Split(dbo.ReplaceYK(@SearchText),N'')
    ),
    Temp0
    AS
    (
    Select Distinct
        s.DocumentID
        , s.Code
        , s2_Scrutiny.ParentScrutinyID          
    From 
        s2_Document s
        Join s2_State state On state.StateID = s.StateID
        Join Post sender On sender.PostID = s.SenderID
        Join s2_Scrutiny On 
            s2_Scrutiny.DocumentID = s.DocumentID
            And s2_Scrutiny.IsActive = 1
            And s2_Scrutiny.ReferenceID not in (-10, -20)
        Cross Join AllPost 
        Join s2_Producer On s2_Producer.DocumentID = s.DocumentID
        Join PersonPost producerPost On producerPost.PostID = s2_Producer.PostID
        Join Person producerPerson On producerPerson.PersonID = producerPost.PersonID
    Where 
        1 = 1
        And (@States = '' Or (N',' + @States + N',') Like (N'%,' + Cast(s.StateID as nvarchar(max)) + ',%'))
        And (@Senders = '' Or (N',' + @Senders + N',') Like (N'%,' + Cast(s.SenderID as nvarchar(max)) + ',%'))
        And (@Date1 = '' Or s.RegistrationDate >= @Date1)
        And (@Date2 = '' Or s.RegistrationDate <= @Date2)
        And (@Filter1ID = '' 
            Or Exists(
                Select 1 
                From 
                    s2_FieldValue fv 
                    Join s2_Field f On f.FieldID = fv.FieldID
                Where 
                    fv.DocumentID = s.DocumentID 
                    And fv.FieldID = @Filter1ID 
                    And (
                        (f.FieldTypeID in (0, 5/*فیلد محاسباتی*/) And fv.[Value] = @Filter1Value)
                        Or (f.FieldTypeID = 3 And Cast(fv.[FieldOptionID] as nvarchar(max)) = @Filter1Value)
                        Or (f.FieldTypeID in(1,2,4))
                    )
            ))
        --پیشنهاد به پست یا نوع پستی که این شخص حائز آن است، ارجاع شده است
        And AllPost.PostID = s2_Scrutiny.ReferenceID                
    ), Temp1
    AS
    (
        Select Distinct
            s.DocumentID
            ,Likeness = 99999999
        From Temp0 s
        Where @SearchText != '' And @SearchText = ISNULL(s.Code, s.DocumentID)
        Union
        Select Distinct
            s.DocumentID
            ,Likeness = SUM(ts.[Length])
        From 
            Temp0 s
            Join s2_TextSegment ts On 
                ts.TableName = N's2_Document' 
                And ts.FieldName = N'Subject'
                And ts.RecordID = s.DocumentID
        Where @SearchText != '' And @SearchText != ISNULL(s.Code, s.DocumentID)
        Group by s.DocumentID
        Union
        Select Distinct
            s.DocumentID
            ,Likeness = 1
        From Temp0 s            
        Where @SearchText = ''
    )
    , Temp2
    AS
    (
        Select t0.*, t1.Likeness
        From
            Temp0 t0
            Join Temp1 t1 On t0.DocumentID = t1.DocumentID
    )
    Insert Into @temp0 (DocumentID, Likeness, ParentScrutinyID)
    Select DocumentID, Likeness, ParentScrutinyID From Temp2

    DECLARE @temp1 TABLE (
        DocumentID int  
        , Likeness int  
    )   


    If @independentPostExists = 0
    Begin
        Insert Into @temp1 (DocumentID, Likeness)
        Select 
            t.DocumentID
            , t.Likeness
        From 
            @temp0 t
    End
    ELSE
    Begin--حوزه مستقلی تعریف شده باشد
        --انتقال حوزه فعال باشد
        Insert Into @temp1 (DocumentID, Likeness)
        Select 
            t.DocumentID
            , t.Likeness
        From 
            @temp0 t
            Join s2_Scrutiny parentScrutiny On parentScrutiny.ScrutinyID = t.ParentScrutinyID
            Join s2_ScrutinyItem sci On sci.ScrutinyItemID = parentScrutiny.ScrutinyItemID
        Where       
            sci.TransferArea = 1

        -- شخص جاری در حوزه ارجاع دهنده باشد
        Insert Into @temp1 (DocumentID, Likeness)
        Select 
            t.DocumentID
            , t.Likeness
        From 
            @temp0 t
            Join s2_Scrutiny parentScrutiny On parentScrutiny.ScrutinyID = t.ParentScrutinyID
            Join Temp_SubalternPost tsp1 On tsp1.Subaltern_PostID = parentScrutiny.ScrutinierID 
            Join Temp_SubalternPost tsp2 On tsp2.Superior_PostID = tsp1.Superior_PostID
        Where       
            tsp1.Superior_NearestIndependent = 1
            And tsp2.Subaltern_PostID = @PersonalPostID 
            --And Not Exists(Select 1 From @temp0 tt Where tt.DocumentID = t.DocumentID)
    End

    Select Count(Distinct t.DocumentID) From @temp1 t Where Likeness > 0

END--end procedure
GO

This code takes 26 seconds:

exec sp_executesql N'Exec [dbo].[s2_GetReceivedDocumentsCount] @username=N'admin', @Filter1ID=N'12',@Filter1Value=N'17658'

BUT : I tested this code for another state but returned 22,000 records in 3 seconds

exec sp_executesql N'Exec [dbo].[s2_GetReceivedDocumentsCount] @username=N'admin'

In this code I removed the @Filter1ID=N'12',@Filter1Value=N'17658'

When I remove this @Filter1ID it not enter here:

And (@Filter1ID = '' 
    Or Exists(
        Select 1 
        From 
            s2_FieldValue fv 
            Join s2_Field f On f.FieldID = fv.FieldID
        Where 
            fv.DocumentID = s.DocumentID 
            And fv.FieldID = @Filter1ID 
            And (
                (f.FieldTypeID in (0, 5/*فیلد محاسباتی*/) And fv.[Value] = @Filter1Value)
                Or (f.FieldTypeID = 3 And Cast(fv.[FieldOptionID] as nvarchar(max)) = @Filter1Value)
                Or (f.FieldTypeID in(1,2,4))
            )

    ))

Now I'm sure problem is here. But where is it? Where is the problem?

A LOT could be different. Indexes, query plans not setup for them, or any number of other reasons. Do you have execution plan? — Brad, Jul 29 '19 at 18:50
@Brad In this code that already has something that can help? — mr-dortaj, Jul 29 '19 at 18:52
I once had a strange slowdown with EXISTS. The execution plan chose nested loops joins trying to find 1 row. However, the several medium sized tables hash matched together fairly quickly. If there no existent rows, the query engine may be choosing a bad plan. Trying IF SELECT(COUNT(*) >= 1. It's a longshot but stranger things have happened. — Robert Sievers, Jul 29 '19 at 18:54
sometimes instead of `f.FieldTypeID in(1,2,4)` test `OR ( f.FieldTypeID=1 OR f.FieldTypeID=2 OR f.FieldTypeID=4)` — Mark Schultheiss, Jul 29 '19 at 19:08
Before looking at rewriting it, just try executing the SELECT within the exists clause on its own. If it runs super fast, try putting it inside a IF EXISTS (such and such) PRINT 'Something existed'. If the time difference is substantial, then you know this is the path to pursue. Odds are, it will be fairly similar, and my longshot advice will turn out to be just that, a long shot. — Robert Sievers, Jul 29 '19 at 19:08
There are many many issues here. First is the dataype of your parameters. Why is everything nvarchar(max)? That is going to cause problems, and not just for performance. Then you have several user defined functions. One appears to be a table valued function, but I would be willing to bet that it isn't an inline table valued function and that your splitter has a loop in it. Then the parameter to that is the result of a scalar function. You have multiple nonSARGable predicates in your where clauses. Last but not least, you fill a table variable (should be a temp table) and then filter it. — Sean Lange, Jul 29 '19 at 19:20
Oh yeah...and you are calling your procedure with dynamic sql. This is just adding another layer complexity for zero benefit. — Sean Lange, Jul 29 '19 at 19:21
@SeanLange i shuold remove the exist . What a good alternative for `exis`؟ — mr-dortaj, Jul 29 '19 at 19:36
That is what you got out of my comment? That you should remove EXISTS? I did not say that nor give any indication of that. Instead you should get rid of the scalar functions, find a better splitter, stop using functions around columns in the where clause, use appropriate datatypes (https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type), nvarchar(max) has no business being the datatype for any of the stuff you have here. To be honest, your target of 22,000 rows in 3 seconds is horribly slow. This should be fast but it needs a LOT of help. — Sean Lange, Jul 29 '19 at 19:52
Trust me that is not the only issue for performance you have here. But feel free to remove the EXISTS. Nobody can really help you here though because we have no idea what your table structures are. You need to provide those details along with some data and the desired output. [Here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great place to start. — Sean Lange, Jul 29 '19 at 20:13

Alex · Answer 1 · 2020-06-21T06:34:15.923

While correlated EXISTS clauses can be a real problem, your overall SP is such a dogs breakfast that I think you should focus on other aspects first:

Do not use LIKE to match a list of numeric values: (N',' + @States + N',') Like (N'%,' + Cast(s.StateID as nvarchar(max)) + ',%'))

Since you already utilise string splitting function, you should split input value into a table variable of stateIDs (make sure that data type is the same as s.StateID and join it. Do the same for @Senders.
Minimise the use of OR as this kills performance really quickly:

This And (@Date1 = '' Or s.RegistrationDate >= @Date1) should be replaced by: SET @Date1 = CASE WHEN '' THEN '01-Jan-1753' ELSE @Date1 END and then in your query you can simply have And s.RegistrationDate >= @Date1.

For @Date2 use the maximum value as per DATETIME reference.
Get rid of NVARCHAR( MAX ). Unless you realistically expect input values to be more than 4000 characters, you should use NVARCHAR( 2000 ) or something smaller.
There is a major performance difference between UNION and UNION ALL. Make sure you use UNION ALL unless you need to remove duplicate records. See this
And lastly EXISTS: without fully knowing your table structure and being able to run the query myself I cannot see a way of removing it, such that it will definitely improve performance.

Offer to increase performance in stored procedure

1 Answers1