CONTAINS vs. CONTAINSTABLE performance

We are implementing an FTS to support google-like drop down boxes.
When I originally constructed my query using a "Contains" parameter,
performance was in the neighborhood of 4 seconds. However, when we
implemented CONTAINSQUERY, performance was on-par with our
expectations (far less than a second).
The table being searched contains < 500 rows total, and the final
results set is < 10 rows at this juncture. But this is just on a
development database, and ultimately we expected thousands of rows
instead of hundreds.
Can anyone tell me why the performance is so dramatically different
and is there something common in the building of FTS queries that we
could do differently?
I'd post the queries, but really there is no significant difference
between them and the example queries except that there are multiple
tables involved.
Thanks!
Both of them can produce very different execution plans. Containstable also
allows you to limit your results set to a definite amount which has
performance benefits.
"Kerry" <maclean.kerry@.gmail.com> wrote in message
news:1177958407.967589.256680@.l77g2000hsb.googlegr oups.com...
> We are implementing an FTS to support google-like drop down boxes.
> When I originally constructed my query using a "Contains" parameter,
> performance was in the neighborhood of 4 seconds. However, when we
> implemented CONTAINSQUERY, performance was on-par with our
> expectations (far less than a second).
> The table being searched contains < 500 rows total, and the final
> results set is < 10 rows at this juncture. But this is just on a
> development database, and ultimately we expected thousands of rows
> instead of hundreds.
> Can anyone tell me why the performance is so dramatically different
> and is there something common in the building of FTS queries that we
> could do differently?
> I'd post the queries, but really there is no significant difference
> between them and the example queries except that there are multiple
> tables involved.
> Thanks!
>
|||We did some internal testing on the indexed-table using both CONTAINS
and CONTAINSTABLE. In both cases, the execution plan was virtually
identical, and when querying just the single table, performance is
fast.
However, when used in our live query, which joins 9 tables from two
different databases, the CONTAINSTABLE join maintains the original
speed, but CONTAINS takes the 4 seconds I mentioned. I used SET
SHOWPLAN_ALL when running the queries both ways, and there seems to be
a negligible difference in the execution plans, although the
CONTAINTABLE plan is marginally faster than CONTAINS.
The bottom line is that this is a very dramatic difference and if we
could understand why the performance difference between the two, we
would be much more comfortable taking this to our production machines.
Thanks for both your responses!

Constructing a field name

Can a field name be constructed, and if so, how?
For example;
Declare @.Date SmallDateTime
Set @.Date = GetDate()
I would like to create a field called 2005_Sales in a temp table
called 2005_Sales by constructing it from:
Convert( char(4), year( @.Date) ) + '_Sales'
If a field can not be created can a field be aliased by the same
construction?
Any help would be appreciated.>> Any help would be appreciated. <<
You are supposed to know to know what a thing is before you do it.
Even more basic, a field is NOT ANYTHING WHATSOEVER like a column.
You are trying to mimic a 1950's file system report in SQL.
Please, please, please learn the basics of data modeling before you
kill people or bankrupt companies. This is the kidn of thing which you
woudl learn in the first two days of a course.