CODEWITHSUNDEEP
.netsqlsql-serversql-server-2005sql-server-2008
RuSh
RuSh

Reputation: 1693

SQL Server 2008 Full Text Search – SLOW

I'm using asp.net with SQL Server 2008 SP2

I have one table with Full Text Search Enabled.

My Hardware is: 2GHZ Quad-Core, 4GB RAM, SAS HDD.

The table has around 5 Million records, and its structure is:

Pages table:

ID (INT)
GroupID (INT)
GroupStart (bit)
Col1 varchar(900)
Col2 nvarchar(450)
Col3 nvarchar(450)
Col4 nvarchar(450)
Col5 nvarchar(450)

i have a fts index on the primary key ID.

I'm using the following query to search the table:

SELECT * FROM (  
SELECT * , ROW_NUMBER()  OVER(  ORDER BY  KEY_TBL.Rank DESC  ) AS RowNumber , COUNT(*) OVER() as TotalRows  
FROM Pages p  
INNER JOIN  
CONTAINSTABLE(Pages, *,N' FORMSOF (INFLECTIONAL, movies)') AS KEY_TBL  
ON p.ID = KEY_TBL.[KEY]  
WHERE  (p.GroupID IS NULL OR p.GroupStart = 1) 
) LS  
WHERE RowNumber BETWEEN 0 AND 10  
ORDER BY RowNumber ASC;

The query takes 1s to complete if total rows is about 500.

and takes 5s to complete if total rows is about 10,000.

and takes 60s to complete if total rows is about 100,000.

Any idea why is the query taking so long?

Is there something wrong in my query , sql server or is it a hardware problem?

Upvotes: 1

Views: 1250

Answers (2)

Pavel Urbančík
Pavel Urbančík

Reputation: 1496

Consider restricting number of matches returned by CONTAINSTABLE -> CONTAINSTABLE(Pages, *,N' FORMSOF (INFLECTIONAL, movies)', 100 )

Upvotes: 1

Brian Smith
Brian Smith

Reputation: 367

Full text search is only useful to search text within varchar or text fields, it will not speed up queries. You would need to add more indexes to the table to do that.

In general the query seems overly complicated, but you haven't explained what the query needs to do. I think the query needs to be optimized.

Upvotes: 1

Related Questions