I have a poorly performing full-text query and I'm wondering if there is
anything I can do to try and improve it or if the current performance is the
best I can hope for.
The query is a relatively simple CONTAINSTABLE which joins to the base table.
Here it is:
SELECT WorkCode, WorkName, RANK
FROM [Work.WorkName] wname
INNER JOIN CONTAINSTABLE ([Work.WorkName], WorkName,
'"heavenly*" OR "day*"', 1000) AS Title_TBL
ON wname.WorkNameID = Title_TBL.[KEY]
It queries a table of song titles for songs with the words "heavenly" and
"day", limiting the results to the best 1000 matches.
The table has about 9million rows.
The database server has 4 CPUs and 4GB RAM. The SQL server max RAM is set to
2GB. The SQL Server version is 9.0.1399.
The table has it's own FT Catalog, on a different disk than the data file and
has recently been repopulated.
Currently the query takes about 10s. I would have thought that even for a
large table like this the query would be of the order of a second or so?
Thanks in advance for any suggestions.
Message posted via SQLMonster.com