Logical Reads of Query is High

  • 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 ?

  • 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]

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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