July 27, 2011 at 10:19 am
I have a query like this one :
SELECT (fields_from_tables_and_views) FROM (some tables and views)
WHERE some_filter
AND view_here.RequestID IN
(SELECT RequestID FROM FullText WHERE ClientID = 61 AND CONTAINS(FullTextRepository.Text, '"some_text*"'))
returning 6 rows after ~ 25 seconds
If I try
SELECT (fields_from_tables_and_views) FROM (some tables and views)
WHERE some_filter
AND view_here.RequestID IN
(123, 456, 789, 250, 345, 792)
where the numbers are the 6 ID's then the query is way faster (1-2 seconds)
I examined the execution plan and all the difference comes from the fact that the index seek for the FullText table is estimating ~ 80K rows but instead of this it only generates 6 rows ...
Is there any way to tell the execution engine that the specific sub-query will never return more than ~ 20-30 rows, because the execution plan generated for the 1st query is really silly because of this ("some tables and views" is hiding pretty large tables with millions of rows that are "index scanned" because of the estimated cardinality of the ...
SELECT RequestID FROM FullText WHERE ClientID = 61 AND CONTAINS(FullTextRepository.Text, '"some_text*"'))
)
I forgot to say that I updated the statistics with a FULLSCAN for the FullText table ..
It is also important that I tried to rewrite the query like this:
SELECT (fields_from_tables_and_views)
FROM (some tables and views)
INNER JOIN (SELECT DISTINCT RequestID FROM FullText WHERE ClientID = 61 AND CONTAINS(FullTextRepository.Text, '"some_text*"')) FT
ON another_table.ID = FT.RequestID
WHERE some_filter
and it was even slower than the 1st question.... 🙁
July 27, 2011 at 10:34 am
can you post the actual execution plan plz?
July 27, 2011 at 10:37 am
Full text indexes and fulltext predicates are very prone to poor estimation. They don't use the column statistics (can't, the statistics are for equality or range, not like).
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 27, 2011 at 10:41 am
GilaMonster (7/27/2011)
Full text indexes and fulltext predicates are very prone to poor estimation. They don't use the column statistics (can't, the statistics are for equality or range, not like).
Then there's only 1 obvious option left.
Create a temp table. Put a Clustered PK on requestid. That'll give you accurate estimates for the rest of the query.
July 27, 2011 at 10:48 am
This is definitely a very good solution, THANK YOU!
I was looking for a hint to force the execution of subquery first, but I suppose there isn't one, so your solution is probably the best one
July 27, 2011 at 10:52 am
virgilrucsandescu (7/27/2011)
This is definitely a very good solution, THANK YOU!I was looking for a hint to force the execution of subquery first, but I suppose there isn't one, so your solution is probably the best one
Hints are never rarrrrrrrrrrrrrellllllly the solution. The guys that built the optimiser are really smart. So it,s better to not tie its hands when finding a fast way to return your data ;-).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply