Full Text Search on SQL Server Performance

We are using Sql server 2008 r2. We have a table having around 4 lac records. We are using full text index(column title, name, description) on the table. We have a search procedure on that table using which we are selecting 10 records from the table on the basis of the keyword. When we are searching directly on the database using the SQL Server Management Studio then procedure takes 0 to 3 seconds on the basis of keywords and records in table belonging to that keyword the first time. But the second search brings up a search faster than 开发者_运维知识库3 seconds. Example if we have "xxx" as keyword then suppose in table we have 1000 records then it take only 0 second in displaying the records. But if we search from keyword "yyy" then in table if we have 22000 records matching with that then it take 2 seconds. I have no idea what could be going wrong here. Even when I pass multiple keywords for search the search result time increases. Do I need to keep check something on the SQL Server settings? Please help.

Any suggestions will be appreciated.

---

This is normal. The more results you have the longer it takes to produce the output. Try to paginate the results. If you get the first 100 results 1..100 it will be much faster. Later you could use the same query but 101..200 etc.

Another problem could be RAM. The more results you have the more storage you need to save them before they are returned.

---

Let me get this straight...

1. Running a query the first time is slower than the second time,
2. When your query returns more records it takes longer and
3. When you search for more keywords (increasing the complexity of the filter) it takes longer

Am i getting that right?

If so they all sound like pretty normal behaviours to me.

1. MSSQL does caching of some sort so subsequent runs of the same query will be faster
2. The more records returned, the longer it takes to move the data from SQL server to your machine
3. The more complex the query/conditions the longer it'll take to run

---

In a RDBMS, full text searching can be accomplished in one of two ways.

1. without prior indexing, each row must be scanned for the matching keyword or
2. if the table has been specifically indexed for full text search (using an inverted index), the index is used to identify all rows matching the keyword.

If your query has multiple keywords, in the first method, each row must be searched as many times as there are keywords, and in the second, the index must be searched for each keyword.

If the query requires boolean operations (must include, must not include, etc.), result rows must be constructed by merging multiple result sets.

Thus one should naturally expect processing time to increase linearly for multiple keyword or boolean full text queries.