I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to page.
exec sp_executesql N'
set arithabort off;
set statistics time on;
set transaction isolation level read uncommitted;
With cte as
(
Select
peta_rn = ROW_NUMBER() OVER (ORDER BY d.LastStatusChangedDateTime desc),
d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime,
d.CreatedByAccountID, d.JurisdictionID,
d.LastStatusChangedDateTime as LastStatusChangedDateTime, d.IDate,
d.InstrumentID, d.DocumentStatusID , u.Username, it.Abbreviation AS ITypeAbbreviation,
ig.Abbreviation AS IGroupAbbreviation, d.DocumentDate
From ITypes it
Inner Join Documents d on it.ITypeID = d.ITypeID
Inner Join Users u on d.UserID = u.UserID
Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
Where
1=1 AND
( d.DocumentStatusID = 9 ) And
(CreatedByAccountID = @0 Or DocumentStatusID = @1 Or DocumentStatusID = @2) And
( d.IGroupID = @3 Or d.IGroupID = @4 ) And
(d.JurisdictionID = @5 Or DocumentStatusID = @6 Or DocumentStatusID = @7)
)
Select
cte.DocumentID, cte.IsReEfiled, cte.IGroupID, cte.ITypeID, cte.RecordingDateTime,
cte.CreatedByAccountID, cte.JurisdictionID,
cte.LastStatusChangedDateTime as 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 COUNT(*) FROM CTE) AS TotalRecords
FROM cte
Inner Join DocumentStatuses ds On cte.DocumentStatusID = ds.DocumentStatusID
Left Join InstrumentFiles inf On cte.DocumentID = inf.DocumentID
Inner Join Jurisdictions j on j.JurisdictionID = cte.JurisdictionID
Where
1=1 And
peta_rn>@8 AND
peta_rn<=@9
Order by peta_rn',
N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int',
@0=44,@1=5,@2=9,@3=3,@4=4,@5=1,@6=5,@7=9,@8=90000,@9=90050
This is my statistics output:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(50 row(s) affected)
SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 727 ms.
and this is my query plan XML. All indexes are created properly. I am unable to post here due to body limit of 30K.
https://www.dropbox.com/s/vrx5ec6cpi8vn33/plan.txt
Any help would be appreciated. Thanks!
Best Answer
First of all, as you are getting sub-second performance how much time do you want to spend on this? If you spend an hour on it, and you tune it to half a second, how long will it take to pay for itself? If this query runs several times a second, then I'm with you, let's tune it. Otherwise it's not really worth it.
Having said that, there are a number of warning signs in your query and plan which need closer attention, if you have time. This is a strange looking query - is it dynamically generated?
1) Rowcount Discrepancy
The dbo.IGroupes table is showing Estimated Rows (7) * Estimated Executions (100.99), so ~707 rows, versus 630,346 Actual Rows. Rowcounts which are more of an order of magnitude difference tend to be a bit of a warning sign. So why do you think this is? What is the relationship between the dbo.Documents table and the dbo.IGroupes table? Should you be getting a distinct list from here instead? This would also mean the estimated cost for this operator is way off too. There are other rowcount discrepancies and Plan Explorer does a good job of showing this:
2) Optimizer Timeout
If you click the SELECT operator in the plan and look at the properties, you will see the property "Reason for Early Termination of Statement Optimization" marked as "Time Out". This means the optimizer ran out of "time" (more like iterations than time) and returned with the lowest estimated cost plan it had at the time. This is generally an indication the query is too complicated and the workaround is to simplify. Looking at your query you have the dummy 1=1 clause and a number of other OR clauses. Can this be simplified?
A few other points: you should be aware that if you have the "Include Actual Execution Plan" option on, this negatively affects the values from SET STATISTICS TIME ON. This is the Observer Effect. Are your values from a run with the plan on or off?
One of my normal approaches to query tuning is to break it up. This helps identify true bottlenecks. It may be that later on I put the query back together. Here's one example, but it's worth noting in my test rig, there's no real performance difference between the original query and this one:
If you want to carry on, please provide the schema and indexes from your tables, plus either the stats or an idea of the rowcounts.