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!