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
        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
            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)
    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
        1=1 And
        peta_rn>@8 AND
    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',

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.


Any help would be appreciated. Thanks!

  • I see that the query plan starts by recommending an index on [Test].[dbo].[Documents] for the column [DocumentStatusID]. It may be valuable to add the addtional columns suggested, but there are limits to the recommendation feature. It may be that DocumentStatusID is in one of your indexes, but the SQL Server believes it should be the first column in an index. – RLF Oct 10 '13 at 12:27
  • I think it's screaming about the missing index because it wants IGroupID in the key. There's a nasty-looking Nested Loops/Table Scan on the IGroupes table that might go away on its own if the index is changed per the suggestion (only the key). It looks like you can move the joins to Users and ITypes outside the CTE so they run after the filtering happens instead of before (unless those joins are implicitly doing filtering on nullable fields). It's unclear which table CreatedByAccountID belongs to as there's no table alias. – Jon Seigel Oct 10 '13 at 17:29

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:

enter image description here

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 OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

      IDENTITY( INT, 1, 1 ) rn
    , CAST( d.DocumentID AS INT ) DocumentID
    , d.IsReEfiled
    , d.IGroupID
    , d.ITypeID
    , d.RecordingDateTime
    , d.CreatedByAccountID
    , d.JurisdictionID
    , d.LastStatusChangedDateTime
    , d.IDate
    , d.InstrumentID
    , d.DocumentStatusID
    , d.DocumentDate
    , d.UserId

INTO #tmp
FROM Documents d
WHERE ( d.DocumentStatusID = 9 )
  AND ( d.CreatedByAccountID = @0 OR d.DocumentStatusID = @1 OR d.DocumentStatusID = @2 )
  AND ( d.IGroupID = @3 Or d.IGroupID = @4 )
  AND (d.JurisdictionID = @5 Or d.DocumentStatusID = @6 Or d.DocumentStatusID = @7 )
ORDER BY d.LastStatusChangedDateTime DESC

    , t.IsReEfiled
    , t.IGroupID
    , t.ITypeID
    , t.RecordingDateTime
    , t.CreatedByAccountID
    , t.JurisdictionID
    , t.LastStatusChangedDateTime
    , t.IDate
    , t.InstrumentID
    , t.DocumentStatusID
    , ig.Abbreviation AS IGroupAbbreviation
    , u.Username
    , j.JDAbbreviation
    , inf.DocumentName
    , it.Abbreviation AS ITypeAbbreviation
    , t.DocumentDate
    , it.Abbreviation AS DocumentStatusAbbreviation
    , ds.Name AS DocumentStatusName
    , ( SELECT COUNT(*) FROM #tmp ) AS TotalRecords
FROM #tmp t
    INNER JOIN dbo.ITypes it ON t.ITypeID = it.ITypeID
    INNER JOIN dbo.Users u ON t.UserID = u.UserID
    INNER JOIN dbo.IGroupes ig ON t.IGroupID = ig.IGroupID
    INNER JOIN dbo.DocumentStatuses ds On t.DocumentStatusID = ds.DocumentStatusID 
    INNER JOIN dbo.Jurisdictions j on t.JurisdictionID = j.JurisdictionID 
    LEFT JOIN dbo.InstrumentFiles inf On t.DocumentID = inf.DocumentID
WHERE rn > @8 AND rn <= @9

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.

  • Hello, WBob! Excellent answer!!! and I confirm that removing IGroupes table from join bring the execution time to 343 ms - a very acceptable time for me. However, the next question is - why would it return actual rows about 600K rows? It is a simple table with 7 rows and it's inner join not cross join so why would it return above 600K records? It just contains IGroupID, Abbrevation and 2 other fields. Please suggest. – Jack Oct 11 '13 at 3:59
  • It's because it's on the outer side of a nested loop; 90,050 * 7 = 630,350. Normally smaller tables would be on the inner side of a nested loop. This might be a side-effect of the optimizer timeout, or the rowcount discrepancy on the IX_LastStatusChangedDateTime scan. How does the broken down query perform versus yours? Can you provide schema and indexes? Some of your indexes look rather wide is there a big overhead for INSERTs into dbo.Documents? – wBob Oct 11 '13 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.