ansaurus

Question

SQL Server 2005 FREETEXT() Perfomance Issue

Answer 1

+1 A:

This issue might arise due to a poor cardinality estimate of the number of results that will be returned by the full text query leading to a poor strategy for the JOIN operations.

How do you find performance if you break it into 2 steps?

One new step that populates a temporary table or table variable with the results of the Full Text query and the second one changing your existing query to refer to the temp table instead.

(NB: You might want to try this JOIN with and without OPTION(RECOMPILE) whilst looking at query plans for (A) a free text search term that returns many results (B) One that returns only a handful of results.)

Edit It's difficult to clarify exactly in the absence of the offending query but what I mean is instead of doing

SELECT <col-list>
FROM --Some 6 table Join
WHERE FREETEXT(...);

How does this perform?

DECLARE @Table TABLE
(
<pk-col-list>
)
INSERT INTO @Table
SELECT PK
FROM YourTable
WHERE FREETEXT(...)

SELECT <col-list>
FROM --Some 6 table Join including onto @Table
OPTION(RECOMPILE)

Martin Smith 2010-05-31 15:36:07

What do you mean by 'How do you find performance if you break it into 2 steps?' ? That doesn'tquite make sense to me in the current context.I'll post the execution plan tomorrow at work, but it's a huge query (pagination with rownumber over two joined subqueries and a lot of joins), so there's definitely a lot of possible performance tuning to be made there. But as I said, I'm wondering why it takes roughly 50 times longer to execute all of a sudden when the query was last changed over a month ago (added an additional where clause) and worked fine up until 4 days ago...

Zenon 2010-05-31 18:45:31

Yep would need to see the query plan to take a guess on that.

Martin Smith 2010-05-31 19:11:17

ok i tried it with the ´DECLARE @Table´ and ´OPTION(RECOMPILE)´ and it takes 3 seconds, as does the original query.But I'll have to test it when the problem arises again.By the way, when I remove the constraints to search only active (status=3) rows from the query, so searching all rows (roughly a million) instead of the 19'000 rows, the query still takes 3 seconds, so I doubt it's because some database entries have been set to inactive over the last 3 days.

Zenon 2010-06-01 13:47:32

Answer 2

A:

Usually when we have this issue, it is because of table fragmentation and stale statistics on the indexes in question.

Next time, try to EXEC sp_updatestats after a rebuild/reindex.

See Using Statistics to Improve Query Performance for more info.

GalacticJello 2010-06-02 22:05:38

Yes that's a possibility as well though it wouldn't explain why it works for a LIKE query running off the same statistics.

Martin Smith 2010-06-02 22:08:42

FREETEXT and LIKE are using different statistics, I believe (FEETEXT is treated like a remote source, vs. actual index statistics for the LIKE).

GalacticJello 2010-06-03 15:53:18

@GalacticJello Correct. FREETEXT in SQL2005 doesn't have any statistics available to make any cardinality estimates at all and will be unaffected by sp_updatestats (always assuming 1 row will be returned) hence the suggestion in my answer to split it out. So the point I was making is that it would be odd (though possible) that a query that relied more heavily on the statistics didn't encounter the same problem.

Martin Smith 2010-06-05 08:48:41

ansaurus

tags:

views:

answers:

SQL Server 2005 FREETEXT() Perfomance Issue

related questions