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)