Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Yarrr
Starting Member
2 Posts |
 Posted - 2006-08-17 : 10:51:09
|
| Situation: Custom forum software search using full-text; the forum has close to 4 million records (posts) and LOTS of users on during the day. Solid, regularly rebuilt indexes are in place, the front end .NET code is pretty sharp and keeps db calls to a minimum, and things load lickety-split, so it's not a matter of general table optimization / application design. The full-text catalogs are kept up to date using Change Tracking that runs in the background.To keep the search running fast I'm using a CONTAINSTABLE with "top_n_by_rank" set at 1000 like so:...INNER JOIN CONTAINSTABLE(Forum_Posts, PostMessage, '"search term here"', 1000) AS T2 ON T2.[KEY] = T1.PostID...I'm trying (without any success thus far) to find a way to get the search to return only the 1000 most recent results on searches that have more than 1000 actual results, while still running fast. Setting the top_n_by_rank is often times a "chronological crapshoot" in terms of what records are returned. For example, a user may post about "German beer". A few hours later they want to find their post and can't remember where it is, so they search for "German beer". The search yields 1000 results for them, but their post is not among them even though it was the most recently written message with "German beer" in it.Is there anything else I can do with this query/search, aside from slowing down the search by selecting a much larger "top_n_by_rank" then ordering that by the 1000 most recent results, that will still result in the same lightning fast searches that our users are used to while accurately retrieving only the newest matches first? Setting a ROWCOUNT or doing a "SELECT TOP [number]" is pointless with this because it only affects the results returned by the CONTAINSTABLE search.I hope I've explained my situation succinctly, I'm not much of a writer >< I've scoured the net all morning without much luck, so any sort of suggestions are greatly appreciated! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 13:21:09
|
| Since the CONSTAINABLE returns 1000 records, and joined by key to T1 table, how do you know which records from T1 are returned? What if you did an order by t1.date desc?Peter LarssonHelsingborg, Sweden |
 |
|
|
Yarrr
Starting Member
2 Posts |
Posted - 2006-08-17 : 14:35:43
|
| The statement snippet above ends with "ORDER BY T1.PostID DESC". This sorts the 1000 "top_n_by_rank" that CONTAINSTABLE returns by newest, but it doesn't have any effect on making the CONTAINSTABLE join select the newest 1000 matching records to begin with, which is the whole problem. |
|
|
|
|
|
|
|
|
Full-text / containstable top_n_by_rank order??