Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-07-17 : 16:38:17
|
| 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.AIDFROM A INNER JOIN CTAON A.AID = CTA.AIDWHERE 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 CTAON A.AID = CTA.AIDWHERE PID = 100 AND CTA.CID IN(200,300)GROUP BY A.AIDTable '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 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-17 : 16:47:10
|
| To start with, your queries are not identical. You have an additional COUNT(CTA.CID) in your second query.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-17 : 16:51:24
|
| Dinakar,Logical reads are the same for table A (i.e. still big) even if I do not count.Thanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-17 : 16:53:55
|
| What are the primary key columns in the tables? When were the indexes updated?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-17 : 17:14:37
|
| Table A: Primary Key = AID (also a clustered index). PID + AID is the non-clustered index I was referring to aboveTable CTA: Primary Key = UID (not shown here)All indexes are updated daily (i.e. 15 hours ago)Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-17 : 23:14:26
|
| Try add index hint in second query to see if logical read drops. |
|
|
|
mattyblah
Starting Member
49 Posts |
Posted - 2007-07-18 : 00:17:01
|
| what is the execution plan? if you can post an image of that it would be of help. |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-07-18 : 15:19:11
|
| Guys,I 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.2Everything looks good now!Thanks a lot! |
|
|
|
|
|
|
|
|
Why such growth in logical reads due to grouping?