Very Computer

Current environment:
SQL Server 6.5 (SP3) on NT 4.0 (sp3)

I have a database with a column called FAC_NAME that I want to be able
to do fast CONTAIN (LIKE)  searches on.  Assume the column/field is
indexed.

If I do an equality type search on this column I get extremely fast
results.

If I do a Begins With LIKE, such as FAC_NAME LIKE 'SMITH%'    I also
get fast results.

But if if do a Contains LIKE, such as FAC_NAME LIKE '%SMITH%'  the
search takes forever.

The table has 450,000 records.  The Equality test obviously uses the
indexes to find the records, and it appears that the Begins With also
uses the indexes, but the Contains evidently just searches the column
without use of the indexes.

I tried making a separate table of the FAC_NAME and key field, and the
CONTAINS search takes about 1/2 the time, but is still slow.

Is there a way to get fast CONTAIN type searches.  Does SQL Server 7.0
provide faster searches with CONTAIN type searches?

Any help or info would be greatly appreciated.

Russ
Calif. Dept. of Toxic Substances Control

Well, SQL Server can't do a binary-style lookup on a value in an index
unless it knows what the value starts with, but it can still benefit from
an index if it "covers" the query.  An index covers a query if all the
fields used by the query are members of the index.  When an index covers a
query, SQL Server may not be able to home in on the record immediately, but
it can find the answer by reading pages only from the index which is much
smaller than the table itself.

In your case, you might either make a single index that contains all the
fields you are making reference to in your query (hopefully not many) or
run two queries where the first query selects only the FAC_NAME column, and
the second uses the value found in the first to quickly find the record
with that name.

The sql optimiser is able to optimise like queries only if the argument is
of the form:

like "Smith%" -- ie the leading characters are specified.

In this case, the optimiser can make a good guess of which indexes to use.

Arguments of the type "%Simth%" are likley to result in a table scan

Steve

--
Stephen Simpson, Simpson Professional Services
127/BH18 8JE/England
Tel: +44(0)1202 777417
Fax: +44(0)1202 604861
Mobile: +44(0)7771 758417

http://www.sps.mcmail.com

Current environment:
SQL Server 6.5 (SP3) on NT 4.0 (sp3)

I have a database with a column called FAC_NAME that I want to be able
to do fast CONTAIN (LIKE)  searches on.  Assume the column/field is
indexed.

If I do an equality type search on this column I get extremely fast
results.

If I do a Begins With LIKE, such as FAC_NAME LIKE 'SMITH%'    I also
get fast results.

But if if do a Contains LIKE, such as FAC_NAME LIKE '%SMITH%'  the
search takes forever.

The table has 450,000 records.  The Equality test obviously uses the
indexes to find the records, and it appears that the Begins With also
uses the indexes, but the Contains evidently just searches the column
without use of the indexes.

I tried making a separate table of the FAC_NAME and key field, and the
CONTAINS search takes about 1/2 the time, but is still slow.

Is there a way to get fast CONTAIN type searches.  Does SQL Server 7.0
provide faster searches with CONTAIN type searches?

Any help or info would be greatly appreciated.

Russ
Calif. Dept. of Toxic Substances Control

Current environment:
SQL Server 6.5 (SP3) on NT 4.0 (sp3)

I have a database with a column called FAC_NAME that I want to be able
to do fast CONTAIN (LIKE)  searches on.  Assume the column/field is
indexed.

If I do an equality type search on this column I get extremely fast
results.

If I do a Begins With LIKE, such as FAC_NAME LIKE 'SMITH%'    I also
get fast results.

But if if do a Contains LIKE, such as FAC_NAME LIKE '%SMITH%'  the
search takes forever.

The table has 450,000 records.  The Equality test obviously uses the
indexes to find the records, and it appears that the Begins With also
uses the indexes, but the Contains evidently just searches the column
without use of the indexes.

I tried making a separate table of the FAC_NAME and key field, and the
CONTAINS search takes about 1/2 the time, but is still slow.

Is there a way to get fast CONTAIN type searches.  Does SQL Server 7.0
provide faster searches with CONTAIN type searches?

Any help or info would be greatly appreciated.

Russ
Calif. Dept. of Toxic Substances Control