Reputation: 7567
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
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:
IGroupID
, ITypeID
, UserID
, DocumentStatusID
, DocumentID
, JurisdictionID
)? That is crucial for fast JOIN performanceUpvotes: 1