I need to count the number of distinct rows returned by a query. The execution plan for this SQL statement includes a "table spool" (temporary table) with the following attributes:
This query takes 100 seconds to count 111 rows. Clearly, the optimizer has chosen a poor plan. If I fetch the actual rows (instead of the count), the query returns in less than a second (and uses an entirely different execution plan, with no temp table).
- Estimated number of rows: 5262
- Actual number of rows: 347,744,315
- Estimated number of executions: 282
- Actual number of executions: 49,988
I have analyzed the query in the tuning advisor and created the recommended statistics and indexes. This has no effect on the query plan or performance.
The query itself is:
select count(distinct record0_.PK)
from Record record0_
join Record record1_ on record1_.EntityFK=record0_.EntityFK
join RecordTextValue textvalues2_ on record1_.PK=textvalues2_.RecordFK
where textvalues2_.FieldFK = 'c08a9473-1308-11e0-b39d-0021869a7edf'
and (textvalues2_.Value like 'o%' escape '~')
What can I do to get reasonable performance from this query? I would be happy to supply the table definitions if that would help.