CONTAINS and WHERE Clause Combination taking too long

waqaramin


Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3

FROM

tbl

WHERE

col1 = '1234'

AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

Jens K. Suessmeyer

As of now, Fulltext index runs separately from the SQL Engine which means that you cannot influence the to be parsed subset of data on the fulltext catalog. Although the data from the relational query will only bring back 1 row, the whole fulltext will be parsed to findt he appropiate matches although they will be discarded later upon joining the two resultsets.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

waqaramin

Hi Jens,

Thanks, i was already affraid that it will be the case.
In this case what you suggest Because LIKE '%%' is killing my performance.
Any suggestion will be appreciated.

Jens K. Suessmeyer

Make sure you optimized the speed of Fulltext (like separate spindles etc).

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---