Re: CONTAINS vs. CONTAINSTABLE performance

Tech-Archive recommends: Fix windows errors by optimizing your registry

Kerry wrote on 30 Apr 2007 11:40:08 -0700:

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!


Load the queries into isql and generate the execution plans, they might help
you to understand why the performance is improved.

In my own tests I found negligible difference between using CONTAINS and
CONTAINSTABLE, as I never use TOP N BY RANK and my queries are on the whole
simple and fit in a single CONTAINS clause using a single table, so the
internal query engine joining and filtering is pretty much the same as that
used when joining the table results with the source table.

Dan


.

Relevant Pages

  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)