The lower right most HASH join produces an estimate of 2k rows but the sources have 2k and 24k estimated row counts. The probe is a PK value and this could just of easily been a NL with a seek on a smaller row set.
I think that low number is causing problems upstream but i don't know how to influence it.
to follow up a bit, i see that statistics are involved somehow but i can't make the numbers in vector or density match.
Also i forced a LOOP join here:
INNER JOIN dbo.tblActualFRTrips at ON ar.DailyRunID = at.DailyRunID
INNER LOOP JOIN dbo.tblActualFRRouteStopTimes arst ON at.DailyTripID = arst.DailyTripID
and performance is much better. I hate to leave that in production code are their other options?
By Dan Holmes 725 asked May 22, 2013 at 02:10 PM
Join cardinality estimation
There are many steps involved in join cardinality estimation. The base table statistics are modified by predicates on those tables (seeks in your case). This operation results in a new, derived histogram (and other statistical information) for each join input. These derived statistics are only used during query plan compilation - they are not visible to end users.
The derived histogram steps from both sides generally will not match up value-wise, so linear interpolation is used to align the steps. Then, additional logic and assumptions are applied to determine a cardinality for the join result. Obviously there are many things that can go wrong here; join cardinality estimation is quite a hard problem.
Fixing the Cardinality Estimate
Where a poor cardinality estimate affects the rest of a large plan, the most reliable way to fix it is to materialize the result of the join (just the columns and rows needed later) in a temporary table. People seem to resist this use of temporary tables for some reason, but it is a sound technique.
On the other hand, if your knowledge of the system and data means you know following the textual join order will always be a reasonable proposition, now and for always, the join hint is fine - though I would add the redundant
My preference would be the temporary table.
By SQLkiwi ♦ 6.6k answered May 23, 2013 at 01:56 AM