question

amit sharma avatar image
amit sharma asked

Tuning full text indexes

Please help me tuning query on full text index SELECT ia.issue_id,AVG(KEY_TBL.[RANK]) AS score_qty, 'issue_attribute' FROM dbo.issue_attribute ia INNER JOIN FREETEXTTABLE(issue_attribute,(attribute_txt),'lots') AS KEY_TBL ON ia.issue_attribute_id = KEY_TBL.[KEY] GROUP BY ia.issue_id This table has 3693557 records and this query is taking 17 second to execute.I have been asked to bring the time to 3 seconds. indexes on this table idx\_issue\_attribute\_alt nonclustered, unique located on PRIMARY issue\_id, attribute\_id, checksum_qty pk\_issue\_attribute clustered, unique, primary key located on PRIMARY issue\_attribute\_id Thanks Suman
performance-tuningfull-text
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Martin 1 avatar image Martin 1 commented ·
Does the performance improve any if you materialise the FullText query into a `#temp` table first then join onto that?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.