CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Jack
Jack

Reputation: 7547

Need help speeding this query

This is my query which takes about 5 seconds fetching 50 records. The total rows in DB are about 100K.

exec sp_executesql N'
set arithabort off;
set transaction isolation level read uncommitted;
With cte 
as (
    Select 
        ROW_NUMBER() OVER (Order By  d.LastStatusChangedDateTime  desc ) peta_rn,
        d.DocumentID
    From Documents d
        Inner Join Users u on d.UserID = u.UserID
        Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
        Inner Join ITypes it on it.ITypeID = d.ITypeID 
    Where 1=1  
        And DateDiff(day, @0, d.LastStatusChangedDateTime ) >= 0  
        And DateDiff(day, d.LastStatusChangedDateTime, @1) >= 0  
        And (d.JurisdictionID = @2 
            Or DocumentStatusID = @3 
            Or DocumentStatusID = @4
            Or DocumentStatusID = @5 
            Or DocumentStatusID = @6) 
) 
Select 
    d.DocumentID, 
    d.IsReEfiled, 
    d.IGroupID, 
    d.ITypeID, 
    d.RecordingDateTime, 
    d.CreatedByAccountID, 
    d.JurisdictionID, 
    d.LastStatusChangedDateTime as LastStatusChangedDateTime,  
    d.IDate, 
    d.InstrumentID, 
    d.DocumentStatusID,
    ig.Abbreviation as IGroupAbbreviation, 
    u.Username, 
    j.JDAbbreviation, 
    inf.DocumentName,
    it.Abbreviation as ITypeAbbreviation, 
    d.DocumentDate, 
    ds.Abbreviation as DocumentStatusAbbreviation,  
    ds.Name as DocumentStatusName,
    (Select 
        Cast(
            Case 
                When d.DocumentID = (   Select  Top 1 doc.DocumentID 
                                        From    Documents doc 
                                        Where   doc.JurisdictionID = d.JurisdictionID 
                                                And doc.DocumentStatusID = d.DocumentStatusID
                                        Order By LastStatusChangedDateTime) 
                Then 1 
                Else 0 
            End as bit))
        as CanChangeStatus,
    Upper((
        Select  Top 1 Stuff((
                        Select  ''='' + dbo.GetDocumentNameFromParams(Business, FirstName, MiddleName, LastName, Abbreviation, NameTypeID)  
                        From    DocumentNames dn
                                Left Join Titles t on dn.TitleID = t.TitleID               
                        Where   DocumentID = d.DocumentID
                        For     XML PATH('''')),1,1,''''))) 
    as FlatDocumentName 
From    
    Documents d 
    Left Join IGroupes ig On d.IGroupID = ig.IGroupID 
    Left Join ITypes it On d.ITypeID = it.ITypeID 
    Left Join Users u On u.UserID = d.UserID 
    Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID 
    Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID
    Left Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
    Inner Join cte on cte.DocumentID = d.DocumentID 
Where   
    1=1 
    And peta_rn>=@13 
    AND peta_rn<=@14 
Order   
    by peta_rn
',N'@0 datetime,@1 datetime,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int,@14 int',
@0='2013-03-01 00:00:00',@1='2013-04-30 00:00:00',@2=1,@3=5,@4=9,@5=4,@6=1,@7=1,@8=5,@9=9,@10=4,@11=1,@12=1,@13=0,@14=50

I confirm the part which takes time is this culprit subquery:

(Select 
        Cast(
            Case 
                When d.DocumentID = (   Select  Top 1 doc.DocumentID 
                                        From    Documents doc 
                                        Where   doc.JurisdictionID = d.JurisdictionID 
                                                And doc.DocumentStatusID = d.DocumentStatusID
                                        Order By LastStatusChangedDateTime) 
                Then 1 
                Else 0 
            End as bit))

As an alternative I tried to use this solution:

exec sp_executesql N'
set arithabort off;
set transaction isolation level read uncommitted;
With cte 
as (
    Select  ROW_NUMBER() OVER (Order By  d.LastStatusChangedDateTime  desc ) peta_rn,
            CASE    WHEN ROW_NUMBER() OVER ( 
                        PARTITION BY d.JurisdictionID, d.DocumentStatusID ORDER BY LastStatusChangedDateTime ) = 1 
                    THEN 1 
                    ELSE 0 
            END 
            AS  CanChangeStatus,
            d.DocumentID
    From    Documents d
            Inner Join Users u on d.UserID = u.UserID
            Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
            Inner Join ITypes it on it.ITypeID = d.ITypeID 
    Where   1=1   
            And (d.JurisdictionID = @2 
                Or DocumentStatusID = @3 
                Or DocumentStatusID = @4
                Or DocumentStatusID = @5 
                Or DocumentStatusID = @6) 
) 
Select 
    d.DocumentID, 
    d.IsReEfiled, 
    d.IGroupID, 
    d.ITypeID, 
    d.RecordingDateTime, 
    d.CreatedByAccountID, 
    d.JurisdictionID, 
    d.LastStatusChangedDateTime as LastStatusChangedDateTime,  
    d.IDate, 
    d.InstrumentID, 
    d.DocumentStatusID,
    ig.Abbreviation as IGroupAbbreviation, 
    u.Username, 
    j.JDAbbreviation, 
    inf.DocumentName,
    it.Abbreviation as ITypeAbbreviation, 
    d.DocumentDate, 
    ds.Abbreviation as DocumentStatusAbbreviation,  
    ds.Name as DocumentStatusName,
    cte.CanChangeStatus,  
    Upper((
        Select  Top 1 Stuff((
                        Select  ''='' + dbo.GetDocumentNameFromParams(Business, FirstName, MiddleName, LastName, Abbreviation, NameTypeID)  
                        From    DocumentNames dn
                                Left Join Titles t on dn.TitleID = t.TitleID               
                        Where   DocumentID = d.DocumentID
                        For     XML PATH('''')),1,1,''''))) 
    as FlatDocumentName                                
From 
    Documents d 
    Left Join IGroupes ig On d.IGroupID = ig.IGroupID 
    Left Join ITypes it On d.ITypeID = it.ITypeID 
    Left Join Users u On u.UserID = d.UserID 
    Left Join DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID
    Left Join InstrumentFiles inf On d.DocumentID = inf.DocumentID 
    Left Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID 
    Inner Join cte on cte.DocumentID = d.DocumentID 
Where 
    1=1 
    And peta_rn>=@13 
    AND peta_rn<=@14 
Order 
    by peta_rn
',N'@0 datetime,@1 datetime,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int,@14 int',
@0='2010-03-01 00:00:00',@1='2013-04-30 00:00:00',@2=1,@3=5,@4=9,@5=4,@6=1,@7=1,@8=5,@9=9,@10=4,@11=1,@12=1,@13=0,@14=50

But it still takes much more than 4 seconds. Ideally, I'd like to expect the result in under 1 second. Is there any way to speed this up? I already have indexes on all search columns and which includes JurisdictionID and DocumentStatusID

Upvotes: 0

Views: 93

Answers (1)

Devart
Devart

Reputation: 121912

Try this one -

exec sp_executesql N'
SET arithabort OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH cte AS 
( 
    SELECT  
          peta_rn = ROW_NUMBER() OVER (ORDER BY d.LastStatusChangedDateTime DESC)  
        , d.DocumentID
        , u.Username
        , it.Abbreviation AS ITypeAbbreviation
        , ig.Abbreviation AS IGroupAbbreviation
        , d.IsReEfiled 
        , d.IGroupID 
        , d.ITypeID 
        , d.RecordingDateTime 
        , d.CreatedByAccountID 
        , d.JurisdictionID
        , d.LastStatusChangedDateTime AS LastStatusChangedDateTime 
        , d.IDate 
        , d.InstrumentID 
        , d.DocumentStatusID
        , d.DocumentDate

    FROM dbo.Documents d
    JOIN dbo.Users u ON d.UserID = u.UserID
    JOIN dbo.IGroupes ig ON ig.IGroupID = d.IGroupID
    JOIN dbo.ITypes it ON it.ITypeID = d.ITypeID
    WHERE DATEDIFF(DAY, @0, d.LastStatusChangedDateTime) >= 0
        AND DATEDIFF(DAY, d.LastStatusChangedDateTime, @1) >= 0
        AND ( 
              d.JurisdictionID = @2
            OR 
              DocumentStatusID IN (@3, @4, @5, @6)
            )
)
SELECT  cte.DocumentID ,
        cte.IsReEfiled ,
        cte.IGroupID ,
        cte.ITypeID ,
        cte.RecordingDateTime ,
        cte.CreatedByAccountID ,
        cte.JurisdictionID ,
        cte.LastStatusChangedDateTime,
        cte.IDate ,
        cte.InstrumentID ,
        cte.DocumentStatusID ,
        cte.IGroupAbbreviation ,
        cte.Username ,
        j.JDAbbreviation ,
        inf.DocumentName ,
        cte.ITypeAbbreviation ,
        cte.DocumentDate ,
        ds.Abbreviation AS DocumentStatusAbbreviation ,
        ds.Name AS DocumentStatusName ,
        ( SELECT CAST(CASE WHEN cte.DocumentID = (
                SELECT TOP 1 doc.DocumentID
                FROM  Documents doc
                WHERE doc.JurisdictionID = cte.JurisdictionID
                        AND doc.DocumentStatusID = cte.DocumentStatusID
                ORDER BY LastStatusChangedDateTime) 
            THEN 1
            ELSE 0
        END AS BIT)
        ) AS CanChangeStatus ,
        UPPER(( SELECT TOP 1
                STUFF(( SELECT '' = ''
                                + dbo.GetDocumentNameFromParams(Business,
                                                    FirstName,
                                                    MiddleName,
                                                    LastName,
                                                    Abbreviation,
                                                    NameTypeID)
                        FROM dbo.DocumentNames dn
                        LEFT JOIN dbo.Titles t ON dn.TitleID = t.TitleID
                        WHERE DocumentID = cte.DocumentID
                        FOR XML PATH('''')), 1, 1, '''')
        )) AS FlatDocumentName
FROM cte
LEFT JOIN dbo.DocumentStatuses ds ON cte.DocumentStatusID = ds.DocumentStatusID
LEFT JOIN dbo.InstrumentFiles inf ON cte.DocumentID = inf.DocumentID
LEFT JOIN dbo.Jurisdictions j ON j.JurisdictionID = cte.JurisdictionID
WHERE peta_rn BETWEEN @13 AND @14
ORDER BY peta_rn',N'@0 datetime,@1 datetime,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int,@14 int',
@0='2013-03-01 00:00:00',@1='2013-04-30 00:00:00',@2=1,@3=5,@4=9,@5=4,@6=1,@7=1,@8=5,@9=9,@10=4,@11=1,@12=1,@13=0,@14=50

Upvotes: 2

Related Questions