Unanswered: SQL 2005 Full-Text performance on large results
I've got a little problem wich i'm trying to solve since 1-2 years and i hoped it would go away with SQL 2005 - but that wasn't the case .
I've just bought a new Server containing:
64 Bit Enviroment
4 GB RAM
2x AMD Opteron 2 GHz Prozeccors (Dual Core)
2x RAID Controllers (RAID 1) containing
2.1 Transaction Logs
I've created a full-text table containing all the search terms i need to search.
RecID - int - Primary Key
SrcID - varchar(30)
ArticleID - int - referring to an original table
SearchField - varchar(150) - Containing the search terms
timestamp - timestamp field
RecID as Primary Key
SearchField as indexed field - Wordbreaker: Neutral (containing several languages), Accent sensitivity off
Now i've got different tables imported in here resulting in a table size of ~ 13 million rows.
There is no problem with the performance on this catalog if i search a term wich isn't contained in more than 200-300 recordsets - but if i search for a term wich could occur in 200'000 upwards it gets extremely slow.
On the slow query the first records get in after no time, but until the query finished up to 60 seconds pass.
The problem is that i have to sort by a ranking value wich is stored externally - so i need all results to sort them...
current (debugging) query:
SELECT ArticleID FROM fullTextTable AS ft INNER JOIN CONTAINSTABLE(FullTextCatalog,SearchField,'"term*" ') AS ftRes ON ftRes.[KEY]=ft.idEntry
Now if i check in the performance monitor:
As soon as i run the query the 'Avg. Disk Read Queue Length' counter on disk D (SQL Data Files) jumps to the top, until the query has finished.
Almost no read/write activity on C: where the Fulltext is stored...
If i rerun the query, after it finished once successfully - it takes place below 1-2 seconds, would be nice to get that result in first place .
I'm aware of the data quantities, but if i take other samples on the same system inner joining tables with similar counts of recordsets its much faster (e.g. counting the fulltext-table entries of each article id - selecting top 200 ordered by count descending - takes ~5 seconds) ...
It seems to be a problem of joining the fulltext result to the fulltext-table.
Is it possible to find out on wich database the avg. read counter on disk D: is done?
I would wonder if it reads that much on the full-text database or tempdb...
I wish I was more knowledgeable of full-text queries and could provide better assistance. We do not get many questions about full-text searches on this forum.
Are you telling me that you have other scenarios that return hundreds of thousands of records from a table containing more than ten million records and sort the result set by an external ranking value joined on what are probably non-indexed columns...and they run fast?
I can tell you that when you run standard SQL statements through query analyzer, sorting the results can easily occupy 30-50% of the processing time.
What does the execution plan for this look like in Query Analyzer?
If it's not practically useful, then it's practically useless.