Logical Reads of Query is High

  • IT researcher

    SSCertifiable

    Points: 7463

    I am using SQL Server 2008 R2. The Queries executed and the IO statistics of the the queries are below.

    Queries:

    Query 1:
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE
    ([TestRec].dbo.[TestRec].[ParentGrpId] IN (SELECT DISTINCT [TestRec].[ParentGrpId] AS [ParentGrpId] FROM [TestRec].dbo.[TestRec] WHERE ( [TestRec].dbo.[TestRec].[ID] IN (228859,228934) )) )
    union all
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE [TestRec].dbo.[TestRec].[CL Id] Is Not Null

    Query 2:
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE
    (
    ([TestRec].dbo.[TestRec].[ParentGrpId] IN (SELECT DISTINCT [TestRec].[ParentGrpId] AS [ParentGrpId] FROM [TestRec].dbo.[TestRec] WHERE ( [TestRec].dbo.[TestRec].[ID] IN (228859,228934) )) )
    OR
    [TestRec].dbo.[TestRec].[CL Id] Is Not Null
    )

    Query 3:
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE
    ([TestRec].dbo.[TestRec].[ParentGrpId] IN (228934) )
    union all
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE [TestRec].dbo.[TestRec].[CL Id] Is Not Null

    Query 4:
    select COUNT(*) from [TestRec].dbo.[TestRec] WHERE
    (
    ([TestRec].dbo.[TestRec].[ParentGrpId] IN (228934) )
    OR
    [TestRec].dbo.[TestRec].[CL Id] Is Not Null
    )

    IO statistics:

    Query 1
    --(2 row(s) affected)
    --Table 'TestRec'. Scan count 4, logical reads 5212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 2
    --(1 row(s) affected)
    --Table 'TestRec'. Scan count 67907, logical reads 206321, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 3
    --(2 row(s) affected)
    --Table 'TestRec'. Scan count 2, logical reads 5206, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 4
    --(1 row(s) affected)
    --Table 'TestRec'. Scan count 1, logical reads 2603, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    The Result is same for all queries.Then why 'logical reads' is high in Query 2 ?

  • Erland Sommarskog

    SSC-Insane

    Points: 23894

    Did you look at the query plans? These are obviously different.

    Generally, I have found that the optimizer often have problems with OR conditions, and rewriting them with UNION or similar results in a better plan and better performance.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Grant Fritchey

    SSC Guru

    Points: 396603

    Yeah, go look at the execution plan for each query. That will show you how the optimizer is using the indexes to satisfy these queries. While a query can logically be the same, returning the same data, clearly they are structurally different. Those structural differences result in variations in performance. This is why some queries will work well as a JOIN and others will work better using CROSS APPLY. The list goes on and on.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply