查询与ROW_NUMBER（）花费过多时间

Question

我在DB各地90K的记录，我运行此查询：

它需要太多的时间（约4秒）：

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 

然而，这第二查询执行1秒：

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 

我的问题是：

1）你能否建议为什么我的第一个查询需要这么长时间来执行？我想优化我的第一个查询，因为它是由一个自动化程序生成的（它的源代码有，但我不能移动Select中的RowNumber）。

2）执行此操作的时间也取决于peta_rn。如果我给peta_rn> 50000和peta_rn < = 50100，则需要执行无望的时间。你能否也请建议为什么要执行的时间取决于我试图读取的行，因为最终我只试图一次读取100行。

Answer 1

只是为了它的乐趣 - 你可以试试这个CTE，看看它是如何执行的？我不完全理解为什么你有三个嵌套子查询有....

;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 

这是否具有相同的响应时间为原始查询？

如果是这样，请检查：

• 你有你的所有外键索引（IGroupID，ITypeID，UserID，DocumentStatusID，DocumentID，JurisdictionID）？这对快速加入性能至关重要