CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jack
Jack

Reputation: 7567

Query with row_number() taking too much time

I have around 90k records in my DB and I run this query:

It takes too much time (around 4 seconds):

SELECT * FROM (SELECT 
    ROW_NUMBER() OVER (Order By DocumentID desc) peta_rn, peta_query.* From 
    (
            Select   d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime, dbo.GetLatestStatusDateTime(d.DocumentID) as LatestStatusDatetime, 
                    dbo.FnCanChangeDocumentStatus(d.DocumentStatusID,d.DocumentID) as CanChangeStatus, 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,
                    dbo.GetFlatDocumentName(d.DocumentID) as FlatDocumentName 
                    From Documents d Inner 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 
                            Inner Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID                           
    ) as peta_query) peta_paged WHERE peta_rn>12000 AND peta_rn<=12100

However this 2nd query executes in 1 second:

SELECT * FROM (SELECT 
    peta_query.* From 
    (
            Select  ROW_NUMBER() OVER (Order By d.DocumentID desc) peta_rn, d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime, dbo.GetLatestStatusDateTime(d.DocumentID) as LatestStatusDatetime, 
                    dbo.FnCanChangeDocumentStatus(d.DocumentStatusID,d.DocumentID) as CanChangeStatus, 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,
                    dbo.GetFlatDocumentName(d.DocumentID) as FlatDocumentName 
                    From Documents d Inner 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 
                            Inner Join Jurisdictions j on j.JurisdictionID = d.JurisdictionID                           
    ) as peta_query) peta_paged WHERE peta_rn>12000 AND peta_rn<=12100

My questions are:

1) Can you please suggest why would my 1st query take so long to execute? I want to optimize my 1st query because it is generated by one of the automated program (whose source I do have but I can't move the RowNumber inside Select).

2) The time to execute this is also dependent on the peta_rn. If I give peta_rn > 50000 and peta_rn <= 50100, it takes hopeless amount of time to execute. Can you please also suggest why would the time to execute depend upon the row that I am trying to fetch since ultimately I am only trying to fetch 100 rows at a time.

Upvotes: 1

Views: 3439

Answers (1)

marc_s
marc_s

Reputation: 755541

Just for the fun of it - can you try this CTE and see how it performs?? I don't totally understand why you have three nested subqueries there.... 

;WITH PetaQuery AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY d.DocumentID DESC) peta_rn, 
        d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime, 
        dbo.GetLatestStatusDateTime(d.DocumentID) as LatestStatusDatetime, 
        dbo.FnCanChangeDocumentStatus(d.DocumentStatusID,d.DocumentID) as CanChangeStatus, 
        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,
        dbo.GetFlatDocumentName(d.DocumentID) as FlatDocumentName 
    FROM 
        Documents d 
    INNER JOIN 
        IGroupes ig On d.IGroupID = ig.IGroupID 
    LEFT OUTER JOIN 
        ITypes it On d.ITypeID = it.ITypeID 
    LEFT OUTER JOIN 
        Users u On u.UserID = d.UserID 
    LEFT OUTER JOIN 
        DocumentStatuses ds On d.DocumentStatusID = ds.DocumentStatusID 
    LEFT OUTER JOIN 
        InstrumentFiles inf On d.DocumentID = inf.DocumentID 
    INNER JOIN 
        Jurisdictions j on j.JurisdictionID = d.JurisdictionID                           
) 
SELECT * FROM PetaQuery
WHERE peta_rn > 12000 AND peta_rn <= 12100

Does this have the same response time as your original query?

If so, please check:

  • do you have indexes on all your foreign keys (IGroupID, ITypeID, UserID, DocumentStatusID, DocumentID, JurisdictionID)? That is crucial for fast JOIN performance

Upvotes: 1

Related Questions