Full text index query plans

I have a table with a full-text index on a given column. If I run the
following:
declare @.s varchar(100)
select @.s = 'fast'
select * from tblHannahRES where contains(RES_SER_TI, @.s)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
on my SQL2000/SP4 box I get substantially different access times, which
is unlike what I would get with a normal index i.e. performing the SQL
below (where the index is on ResID) yields identical plans and access
times:
declare @.i int
select @.i = 1463440
select * from tblHannahRES where ResID = @.i
select * from tblHannahRES where ResID = 1463440
With the full-text indices I get the plans shown below and the second
query is consistenly 6X faster than the first.
One thing I notice is that the remote scan itself costs 0.36 vs 0.06
though both generate the same number of rows. the second thing to
notice is that the first case incurs an extra sorting step (which costs
about 0.02 points). I'm not sure why this should be the case. Can
anyone elucidate on the issue here?
TIA - e
select * from tblHannahRES where contains(RES_SER_TI, @.s)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
| |--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)thoughts anyone?
ekkis wrote:
> I have a table with a full-text index on a given column. If I run the
> following:
> declare @.s varchar(100)
> select @.s = 'fast'
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> on my SQL2000/SP4 box I get substantially different access times, which
> is unlike what I would get with a normal index i.e. performing the SQL
> below (where the index is on ResID) yields identical plans and access
> times:
> declare @.i int
> select @.i = 1463440
> select * from tblHannahRES where ResID = @.i
> select * from tblHannahRES where ResID = 1463440
> With the full-text indices I get the plans shown below and the second
> query is consistenly 6X faster than the first.
> One thing I notice is that the remote scan itself costs 0.36 vs 0.06
> though both generate the same number of rows. the second thing to
> notice is that the first case incurs an extra sorting step (which costs
> about 0.02 points). I'm not sure why this should be the case. Can
> anyone elucidate on the issue here?
> TIA - e
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
> | |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)

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)