SQL Server Performance Forum – Threads Archive
Grouping increases logical reads 80-fold …
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
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
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
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!
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
]]>