January 24, 2011 at 12:31 pm
I'm curious how to calculate the cardinality in this situation.
SELECT * FROM
Person p
INNER JOIN
PersonType pt
on p.PersonTypeID = pt.ID
If you look at the statistics using the DBCC Show_Statistics command the cardinality estimates are easy to determine below. If you ran this query SELECT * from Person where PersonTypeID = 1 the estimate would be 100,581 rows, and the actual matches. When I look at the execution plan and play with this it matches what I think. The second that I join to the PersonType table the cardinality estimate is 9,210.57 with an actual count of 221,325. If the optimiser could not determine the count I would have assumed it would fall back to the total row count since there isn't a predicate value. Not sure how to calculate the math to arrive at 9,210.57, can anyone show me where to multiply the right values to arrive at that total? I tried multipling the "All Density" times the table total but that's 11,066.25.
Name----Updated----Rows----Steps---Density
testJan 25 2011 2:42PM 2213251004NO NULL221325
All density----Average----Length-----Columns
0.1 4 TypeId
RANGE_HI_KEY----RANGE_ROWS-----EQ_ROWS--- DISTINCT_RANGE_ROWS----AVG_RANGE_ROWS
1010058101
1007368501
110212801
120125501
14068601
15038501
16013301
1708501
2003744901
220493801
January 24, 2011 at 8:27 pm
Hi,
I wouldn't bother too much about those non-selective queries. The only way to execute them is table scan and, I guess, hash join. Seems that cardinality isestimated right for the selective query.
Cheers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply