Search base on Fulltext search and varchar columns taking for ever | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Search base on Fulltext search and varchar columns taking for ever

Hi, I have read and worked on fulltext but everytime i stuck when there is a requirement to search database base on fulltext search and combination of other columns like varchar or date fields.
Result, it take forever to search result (or at least for my case where i have 20~30 million records in my database). BTW my server setting is
SQL SERVER 2005 Standard X64.
Windows 2008 X64.
16GB RAM. Lets say my current structure have 2 tables CREATE TABLE Products(ProductID INT IDENTITY(1,1) PRIMARY KEY,ProductName VARCAR(MAX)) CREAET TABLE ProductDetail(FK_ID INT,ItemCode VARCHAR(4),VendorCode VARCHAR(5)) Here Products table keep unique product information whereas ProductDetail keep track of all vendorCode and ItemCode for respective products. Products.ProductID is Primary key and ProductDetail.FK_ID is Foreign key.
I have FullText search on Products.ProductName and Multiple Indexes on ProductDetail.
Everything work smooth as long as i do search base on ProductName but if i start combining sql criteria base on ProductName and ItemCode/VendorCode it take forever or at least take 10~15 seconds to search information (sometimes search is blank).
My Products table have 20 million records and ProductDetail have around 35~40 million records. Any help will be appreciated, all article i read only talk about search base on single FullText search column/columns but not in combination of Fulltext search column and datetime/varchar columns. Regards,
The first question raises about the supporting indexes for thsoe non-FT columns.
How frequently you are performing REORG or REBUILD of FT catalogs?
The full-text key should be a small column or which do not require a join to the DocID mapping table, thus improving performance. It’s also faster if the key is a clustered index. Also check what kind of drivers (RAID type) where the FT related objects are stored.
Satya thank you for your reply.
Due to hardware contraint and small business we have 2 RAID 5 of 7 disk each.
These tables are very static and only updated once in 3 months so i update FT Catalog once tables are updated.
FT column is description fields with column length varchar(50), unfortunately this is not full table structure i have few more columns in same table (i just showed you part of the table to keep is clean).
It is very fast when i do search on FT Column alone but as i start match mixing criteria with other fields it either take very long when there is no row found or even it take 10~15 seconds to return a query with only 4~5 records.
That explains the slow down as RAID5 may be a little source of performance loss, however let us not strikeout other optiosn such as Disk I/O, Memory and disk usage during the intensive query that you are referring.
As this is a 2005 instance why not monitor the process using Activity monitor and also PERFMOn to collect stats during the query operation. Once the statistics data is available we can dig out and look at the execution plan .. for more information.
Thank you again, i will collect some statistics and will bug back ;).

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |