1

When I execute this script

SET STATISTICS IO ON

SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] 
FROM 
    vwTblTest 
WHERE 
    ([Factor] >= 139501 AND [Factor] < 139507) AND 
    ...
ORDER BY 
    [Date] DESC 
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;

The vwTblTest is a view that join two table like this :

SELECT
    [tbl1].*,
    [tbl2].*
FROM
    [tbl1] 
    INNER JOIN
    [tbl2] ON ([tbl2].[Factor] = [tbl1].[Tbl1Factor] 
           AND [tbl2].[ID] = [tbl1].[Tbl2ID]) 
    LEFT JOIN 
    (select Reference, 
            CASE WHEN ISNULL(COUNT(Reference), 0) = 0 THEN 'False' ELSE 'True' END 
              AS HasReference 
     FROM [tbl3] 
     GROUP BY Reference
    ) reference ON reference.Reference = [tbl2].ID

tbl1 contains about 9 million records, and it has a partition on Factor field. If tbl2 contains 25 or more records every thing is fine, But when tbl2 contains less than 25 records statistic report show a lot of logical read to execute script.

Column [Date] is from tbl1. For cross checking with the plans:

tbl1 : doc_LetterInstances 
tbl2 : doc_Letters 
tbl3 : doc_LetterReferences

Statistics :

Scan count 1, logical reads 12754432, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan : Execution plan of script 1

When I change script like bellow statistic report show normal logical reads. OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY was removed.

SET STATISTICS IO ON

SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] 
FROM 
    vwTblTest 
WHERE 
    ([Factor] >= 139501 AND [Factor] < 139507) AND 
    ...
ORDER BY 
    [Date] DESC  

Statistics :

Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan : Execution plan of script 2

What is wrong in my query?

  • Can you show execution plan? – Martin Smith Jul 20 '16 at 6:28
  • 1
    Please give your actual query and details about the table sizes and structure of the three tables involved. – Martin Smith Jul 20 '16 at 9:39
  • 1
    Yes I saw that - but that shows the situation is in fact significantly different from your original question - which indicates only a single table is involved. Presumably the difference is because you are getting a different join strategy. – Martin Smith Jul 20 '16 at 9:41
  • 2
    So can you show the SELECT query behind doc_vwTblTest and the definitions of the relevant tables including indexes? – Martin Smith Jul 20 '16 at 9:50
  • 1
    Thnx, I added the info in the question. – ypercubeᵀᴹ Jul 20 '16 at 11:38

Your Answer

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

Browse other questions tagged or ask your own question.