SQL Server Performance

Grouping increases logical reads 80-fold ...

Discussion in 'T-SQL Performance Tuning for Developers' started by sql_er, Jul 17, 2007.

  1. sql_er New Member

    Guys,

    I've been trying to speed up a slow running query, and I found one of its slow spots, but cannot fix the problem. Basically, I do not understand why, due to a simple change, the # logical reads increases so much. Details are below:

    ************************************************************************************
    Query 1:

    SELECT A.AID
    FROM A INNER JOIN CTA
    ON A.AID = CTA.AID
    WHERE PID = 100 AND CTA.CID IN(200,300)

    Table 'A'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0.
    Table 'CTA'. Scan count 2, logical reads 119, physical reads 0, read-ahead reads 0.

    Query 2:

    SELECT A.AID, COUNT(CTA.CID)
    FROM A INNER JOIN CTA
    ON A.AID = CTA.AID
    WHERE PID = 100 AND CTA.CID IN(200,300)
    GROUP BY A.AID

    Table 'A'. Scan count 20482, logical reads 88531, physical reads 0, read-ahead reads 0.
    Table 'CTA'. Scan count 2, logical reads 119, physical reads 0, read-ahead reads 0.
    ***************************************************************************************
    Looking at the execution plan, I see that in the first case an index seek of a particular non-clustered index occurs. In the second case, an index seek of a CLUSTERED index occurs.

    Can anyone tell me why the non-clustered index is not used in the 2nd case?

    Furthermore, when I used a hint to use the non-clustered index, it worked - i.e. non-clustered index was used. However, the situation was only slightly improved, but reads are nowhere close to the non-grouped query, as shown below:

    Table 'A'. Scan count 20632, logical reads 64760, physical reads 0, read-ahead reads 0.
    Table 'CTA'. Scan count 2, logical reads 119, physical reads 0, read-ahead reads 0.

    Any suggestions?


    Thank you
  2. techbabu303 New Member

    hi,

    Looking at query you have posted drives me to world of how intenals must be working based on my knowledge of older RDBMS.

    In SQL server 2000 i think the database engine would need to sort the data for grouping results n by descending or ascending in your case may be ascending so would increase the logical reads.

    Does not matter if you are using clustered or non clustered index based on your inputs.

    Let me check this thoery or probably experts can eat me....anyway if iam wrong.

    Cheers
    Sat
  3. techbabu303 New Member

    Hi ,

    Here is what I tested in SQL 2000 SP4 , I created two tables without any index and performed

    Query 1:
    ========

    SELECT testa.AID
    FROM testa INNER JOIN testb
    ON AID = bid
    where AID in(20,40)

    Stat Output:
    ===========
    Table 'testb'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
    Table 'testa'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.



    Query 2:
    ========

    SELECT testa.AID
    FROM testa INNER JOIN testb
    ON AID = bid
    where AID in(20,40)
    group by aid

    Stat Output:
    ============
    Table 'Worktable'. Scan count 3, logical reads 8, physical reads 0, read-ahead reads 0.
    Table 'testb'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
    Table 'testa'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.



    The query two had additional worktable created internally which perfomed the group by, probably this explains why read are higher.....let me dig further with indexes.

    Cheers
    Sat
  4. sql_er New Member

    Sat,

    Thank you for your efforts. I actually found out that we did not optimize(update statistics for) our databases for quite a while, although our data was constantly changing. Once I added the Optimization job and ran it to update statistics, the logical read count from table A dropped from 88000 to 343.

    I knew before that updating statistics was important, but could've never imagined that to such an extent.

    I further added a non-clustered index on table A. This further decreased the logical reads to 23 only.

    The subtree cost also dropped from 0.5 to 0.2

    Everything looks good now!


    Thanks a lot!
  5. techbabu303 New Member

    Guess I learnt from this , anyways adding indexes there were no worktables created guess my theory was incorrect !!!

    Thx to You learnt something on this

    Cheers
    Sat

Share This Page