FTS in SQL2005 and JOINS

Hi, I'm having a slight problem with some of my FTS queries, namely it
seems that in SQL 2005 the execution plans are constructed by first
doing the normal query and then joining with the FTS results.
For instance:
SELECT P.id,P.name
FROM CONTAINSTABLE(prd_Names,phrase,'"hp*"') F
INNER JOIN prd_Names P ON F.[key]=P.id
ORDER BY P.name,F.rank;
Causes a massive index scan on prd_Names table first and only then
joins with the smaller set returned by CONTAINSTABLE. IIRC in 2000 I
didn't have this problem (I think it was the other way around).
Is there any way to make the query behave properly?
The DTD is very simple prd_Names is a table with 3 columns
id (pkey) int
name varchar(100)
phrase varchar(500)
a clustered index on id
and an index on the name column.
BTW the table contains ~400k rows.
Thanks.
I've no idea whether this would work, but have you tried to re-order the
query so that the inner join is the other way around?
Griff
|||Griff wrote:
> I've no idea whether this would work, but have you tried to re-order the
> query so that the inner join is the other way around?
Yep, tried that with no effect. I also rewrote it without using the
JOIN statement: WHERE CONTAINS(..) etc. and also WHERE P.id IN (SELECT
... FROM CONTAINSTABLE()) but neither helped.
Thanks.
|||I think I've missed something - why are you doing an inner join in the first
place?
|||Griff wrote:
> I think I've missed something - why are you doing an inner join in the first
> place?
Because CONTAINSTABLE returns a table with [key],[rank] columns which I
need (afaik it's the normal way to use CONTAINSTABLE)