Too many full-text columns or the full-text query is too complex to be executed

Question

Hi,
I am using sql server 2005, I am trying to do full text index
I have a table Tbl_Product_with_Index

CREATE TABLE [dbo].[Tbl_Product_With_Index](
    [Product_Id] [int] NOT NULL,
    [Product_Name] [nvarchar](max) NULL,
    [Quantity] [int] NULL,

)

the data in my table look like

Product_Id   Product_Name                     Quantity

1	Pro_1	1
2	Pro_2	2
3	Pro_3	3
4	Pro_4	4
5	Pro_5	5
6	Pro_6	6
7	Pro_7	7
8	Pro_8	8
9	Pro_9	9

................

i have 499999 rows in my table
when i run the query

SELECT product_name  ,product_Id from Tbl_Product_with_Index 
where contains (product_name,'Pro_2')
the result is 

Pro_2    2

only 1 row ....

and when i run
SELECT product_name  ,product_Id from Tbl_Product_with_Index 
where  product_name like '%Pro_2%'

i have 111111 rows !!!!!!!!!!!!

beside
when  I run
SELECT product_name  ,product_Id from Tbl_Product_with_Index 
where contains (product_name,'"Pro_*"')

I have => Too many full-text columns or the full-text query is too complex to be executed.

Can any one please tell me why there is much difference between like and contains ?
can contains do the same functionality as Like ?
why i have this error, is there is a bug in sql server

Hope to have an answer

Answer 1

!!!!

No answer till now?!!!!!
strange!!!!!

it is a simple question


no body want to help!?

Answer 2

I did my own research

this query:

select * from Tbl_Product_No_Index
where Contains (Product_Name,'"Pro_2*"')

return the same number of rows as

select * from Tbl_Product_No_Index
where Product_Name like '%Pro_2%'

but i still have
Too many full-text columns or the full-text query is too complex to be executed

SELECT product_name  ,product_Id from Tbl_Product_with_Index 
where contains (product_name,'"Pro_*"')

Please help i need an answer plz.

Answer 3

Wael I suspect that the search is actually been conducted on Pro* or even Pro* and * which is why you are getting this problem.

Does it work any better with Pro*?

Hilary

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hi Hilary

10x a lot for replying

In fact I think there is a bug in the SQL 2005

I tried the Queries but it didn't work anymore

I drop the catalogue and recreate it, it works on the first lunch but it didn't on the second lunch and I have this msg:
"Too many full-text columns or the full-text query is too complex to be executed"

I tried the Queries in SQL 2008 and it works so fine and I noticed:

SQL 2008 is much faster even when I use the '%LIKE%'

I don't know why when I run

select * from Tbl_Product_No_Index
where Contains (Product_Name,'" *ro_2*"')
there is no row, I think that Contains does not manipulate the asterisk in the beginning
example Contains (Product_Name,'" *ght*"') does not return light
correct me if I am wrong

if not, is there is any way to return light using full text index (I don't want to use Like '%ght%')

 

10x a lot for you’re helping in advance

Regards

 

 

Answer 5

Hi Wael,

The query works fine in both SQL Server 2005 and SQL Server 2008 on my local machine. Since there is a large amount of data in your table, the search may consume excessive resource. I would suggest you check the issue by using a table with less records or using TOP clause or SET ROWCOUNT to limit the number of returned rows.

Related link:

SET ROWCOUNT (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188774.aspx

SQL Server full-text search is a word-based engine, which considers a word to be a string of characters without spaces or punctuation and is different from the LIKE predicate.

Please refer to the links below:

SQL Server 2008 Full-Text Search: Internals and Enhancements
http://technet.microsoft.com/en-us/library/cc721269.aspx

Pro Full-Text Search in SQL Server 2008  By Michael Coles
http://books.google.com/books?id=wGwVkAt79bEC&pg=PA3&lpg=PA3&dq=sql+server+full+text+search+like+predicate&source=bl&ots=fuiKd76o28&sig=1dflFFjBWBI0F24vxSnvJZ1j2Qk&hl=en&ei=8J96Sp3dMJWTkQWlsrjiAg&sa=X&oi=book_result&ct=result&resnum=4#v=onepage&q=sql%20server

If we would like to do a wildcard search in SQL Server Full-Text Search, we can only add an asterisk at the end of the phrase.  Otherwise, the asterisk will be considered as punctuation and usually be ignored.

Please refer to the following links for more information:

Wildcard search in SQL Server Full Text
http://sqlblogcasts.com/blogs/simons/archive/2005/11/07/Wildcard-search-in-SQL-Server-Full-Text.aspx

CONTAINS (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187787.aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Thanks a lot for your reply

 

in fact i tried

select  top 10 * from  Tbl_Product_No_Index
where contains(Product_Name,'"Pro_*"') in SQL sever 2005 and the result is

Too many full-text columns or the full-text query is too complex to be executed.

I know that the full text index is much more faster then the Predicate Like
I don't know what approach to use if I want to search all the rows that contains "Ght" specially that in my database I have a large number of rows and my objective from the beginning is to find the rows in fastest way without consuming a lot of resources

any advice on this pbs?

Answer 7

Hi Wael / Jian Kang,

I have the same issue on SQL Server 2005.

Is that a known issue with FTS on 2005 or there is a work around for this?

One of our client is trying to search for '"n gamer" which is interpretted as '"n*" and "gamer*"' and comes back with "Too many full-text columns or the full-text query is too complex to be executed"

contains clause in my query reads like this : 

CONTAINS

 

(rv.[Value], @Keyword, LANGUAGE 2057)

Appreciate your advice.

Regards,

Harish

Answer 8

does it work with gamer*?

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 9

Hi Hilary,

Yes it does when just give "gamer*"

its only failing for "n*"  I tried other alphabets but they are all ok except for  "n*" 

Appreciate your advice.

Regards,

Harish

Answer 10

The problem is you are expanding your search onto all words beginning with the letter n and this overwhelms the full-text query engine.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 11

Hi Hilary,

It works fine with other alphabets though like  "a*", "b*", "c*", etc., it just returns this error for "n*". Hence, wondering about any work arounds to overcome this problem.

This seem to work fine in 2008, hence, wondering if its a FTS limitation in 2005

Appreciate your advice.

Regards,

Harish

Answer 12

Do you have a lot of numbers in your data? Numbers will be indexed as follows: 1 is indexed as nn1, 2 as nn2.

So I am wondering if you have a lot of numbers if this might result in a huge number of terms begining with the letter n.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 13

Hi Hilary,

Yes it contains mixture of numbers and text.

Is there a way we can only search on text instead of numbers. I don't think changing data modal is an option in current scenario.

Regards,

Harish

Answer 14

Hello all,

the first thing that made me use full text index instead of like is that the full text index do the index seek on the table

here are 3 queries

select * from
Tbl_Product_No_Index
where Product_Name like '%Pro_1%'

select * from
 Tbl_Product_No_Index
where contains (  Product_Name, 'Pro_1' )

select * from
 Tbl_Product_No_Index
where contains (  Product_Name, '"Pro_1*"' )

I noticed in the execution plan that :
for the 1st query there is no index seek table there is index scan and the query cost is 30% (111111 rows)
for the  2nd query there is index seek table and the query cost is 0% (1 rows)
for the  3rd query there  is no index seek table there is index scan and the query cost is 70% (111111 rows)

So
what benefit we have if we want to use the full index search in this situation?

hope to have an answer

regards
Wael.

Answer 15

fulltext is not the answer for every problem. In this case like might be the best way to solve this problem.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941