July 19, 2011 at 8:34 am
ahhh...so if it has to go the row specified in the where..then it's able to count along the way?
whereas if there is no where clause it has to scan the table/index?
July 19, 2011 at 8:40 am
A seek cannot be done without a where clause. It's just the way ot has to be.
Once that's decided, the server starts counting once it finds the rows that satisfy the query.
July 19, 2011 at 8:49 am
krypto69 (7/19/2011)
ahhh...so if it has to go the row specified in the where..then it's able to count along the way?
Not counting along the way, locating the rows that qualify for the predicate (that can seek) and then counting those.
A seek requires a predicate, something to seek on, something to navigate the index tree to look for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2011 at 9:00 am
understood thanks again
July 19, 2011 at 9:11 am
from my dev:
So, the query in question does scan because of the table size over 500K records?
PayeeMaster table has about 53585 records in dev vs. 555077 in PaymentChild
Compare
SELECT Count('x')
FROM dbo.PaymentChild PC
INNER JOIN dbo.PaymentParent PP ON PP.UserID = PC.UserID AND PP.PaymentRefNo = PC.PaymentRefNo
Where PC.PaymentStatus = 'P'
And
SELECT Count('x')
FROM dbo.PaymentChild PC
INNER JOIN dbo.PaymentParent PP ON PP.UserID = PC.UserID AND PP.PaymentRefNo = PC.PaymentRefNo
Where PC.PaymentStatus = 'AA'
• Payment Status ‘P’ is huge 523286 – index scan
• Payment Status ‘AA’ is small 1362 – index seek
July 19, 2011 at 9:16 am
Without seeing the exec plan, it's a guess, but it's probably scanning because of the larger row count, it's more efficient.
Just one thing, why oh why oh why count('x')? What's wrong with a simple count(*)? Your variation (which, btw, performs and behaves exactly like count(*)) is just going to confuse people.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2011 at 9:19 am
Send them the link I sent you a minutes ago. It's all CLEARLY explained.
July 19, 2011 at 9:23 am
Ninja's_RGR'us (7/19/2011)
Also the estimated # of rows returned must be really low (% of whole table) for the seek to be used as the plan.
Unless the index is covering. If the index is covering SQL will use it no matter the % affected
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2011 at 9:32 am
GilaMonster (7/19/2011)
Ninja's_RGR'us (7/19/2011)
Also the estimated # of rows returned must be really low (% of whole table) for the seek to be used as the plan.Unless the index is covering. If the index is covering SQL will use it no matter the % affected
Of course. I was more in the scan vs seek mode.
count(*) makes any index covering (assuming no join / where).
From the on out seek will never be an option.
July 19, 2011 at 9:35 am
Ninja's_RGR'us (7/19/2011)
count(*) makes any index covering (assuming no join / where).
Indeed. My guess here is that it's the join that's the main factor in the changed plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2011 at 9:44 am
Well it's the 3rd or 4th question about this query already so I guess full optimisation is the next one in the pipe!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply