Performance Issues when using CONTAINSTABLE

Performance Issues when using CONTAINSTABLE

Post by Shadowfa » Tue, 23 Sep 2003 19:37:12

I have a number of tables with Full Text Indexing set up
on some of the columns.

The problem is that performance is badly affected when
retrieving rows using CONTAINSTABLE, as in the following:

INSERT INTO temp_Placements(PlacementID, UserID, Switch)
SELECT DISTINCT PlacementInfo3.PlacementID,
@StudentID, @Switch FROM PlacementInfo3 INNER JOIN
temp_Placements pt ON pt.PlacementID =
PlacementInfo3.PlacementID AND UserID = @StudentID AND
Switch = 0


CONTAINSTABLE(core_company,*, @Keywords)
as cresult1

ON PlacementInfo3.CompanyID = cresult1.

I've looked at MSDN for performance tips and have only
come up with Paging Results. Does anyone know how this may
be effected, or is there anything else I can try?


Performance Issues when using CONTAINSTABLE

Post by Hilary Cot » Tue, 23 Sep 2003 20:46:21

Contains/ContainsTable performance is opimtized for
smaller row sets (under 2000). So is FreeText and

Run showplan to see if you can't get any hints on indexes
to place on your join to improve performance more.

Are there indexes on your temp_Placements table? Under
some circumstances you can get insert performance
impovements by having an index on this table as opposed to
just using the heap.


Performance Issues when using CONTAINSTABLE

Post by John Kan » Wed, 24 Sep 2003 00:39:29

In addition to what Hilary suggests below, could you post the exact version
of SQL Server as well as the OS platform via:

SELECT @@version
SELECT @@language

Additionally, how many rows are in you FT-enable table ()? Depending upon
your answers to these questions, you might be able to take advantage of
CONTAINSTABLE and use its "Top_N_Rank" parameter, see KB article 240833
(Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP"
at ;EN-US;240833