Understanding Total Hits & Paging in the MOSS 2007 Search API
One of the more discussed topics I’ve seen (and struggled with myself) is around the concept of obtaining the total number of hits in a search results when working with the MOSS API. For instance, when I search for “sales forecast” in my SharePoint site, I want to not only see a set of paged results, 10 hits per page, but also see that my search found 127 matches. Those of you who’ve worked with the SharePoint Search Web Parts know this is a piece of cake using the Search Core Results, Search Paging and Search Statistics Web Parts.
But what if you need to roll your own solution? How can you get the same data out of your search query using the MOSS 2007 Search API? At first this can be a bit tricky but this post will hopefully show you how to knock it down to being a trivial task.
Executing a search query against the SharePoint API has you working with two objects that implement the abstract class Microsoft.Office.Server.Search.Query.Query: Microsoft.Office.Server.Search.Query.KeywordQuery and Microsoft.Office.Server.Search.Query.FullTextSqlQuery. The former KeywordQuery is useful for simple queries whereas the latter FullTextSqlQuery is much more powerful. Both implement the Execute() method which executes the defined query and returns back a collection of results as type Microsoft.Office.Server.Search.Query.ResultTableCollection. Using this object, you can get the specific results you are interested in. For instance to get the relevant results use the following to get an instance of a specific Microsoft.Office.Server.Search.Query.ResultTable:
using (FullTextSqlQuery query = new FullTextSqlQuery(SPContext.Current.Site))
{
query.QueryText = "SELECT Rank, Title Url FROM Scope() WHERE FREETEXT(defaultproperties,'sales proposal') ORDER BY Rank Desc",
ResultTableCollection results = query.Execute();
ResultTable relevantResults = results[ResultType.RelevantResults];
// do work with the results
}
Simple enough, but the project requires much more than that as usual. What we need to do is page the results to show only 15 items per page. No problem… let’s just modify that query a bit to set the Query.StartRow & Query.RowLimit properties of the query to say what page we’re on and tell SharePoint how many results we want to get back. Take for instance if we’re on page 2 of the results… we want to start with the 16th hit as 1-15 were on page 1:
using (FullTextSqlQuery query = new FullTextSqlQuery(SPContext.Current.Site))
{
query.StartRow = 16;
query.RowLimit = 15;
query.QueryText = "SELECT Rank, Title Url FROM Scope() WHERE FREETEXT(defaultproperties,'sales proposal') ORDER BY Rank Desc",
ResultTableCollection results = query.Execute();
ResultTable relevantResults = results[ResultType.RelevantResults];
// do work with the results
}
Again… pretty straight forward. Now is where it gets a bit tricky. You need to show links to provide paging… but in order to do that you need a good idea what the total reset set of your search query because if there were only 43 hits, you don’t want to shot options to jump to page 9. The property that gives you the number you’re looking for is ResultTable.TotalResults. Now there’s something special about this guy: he doesn’t give you an exact number… he gives you an estimate. Why an estimate? Quite simply, with all the security trimming and other complex logic inherit to search algorithms, it’s just too expensive to get a specific number. Sites like Live.com can do this because they don’t have to concern themselves with the security trimming of hits.
But this is not all… there’s another property you should pay attention to: Query.TotalRowsExactMinimum. This property tells SharePoint this is the minimum number of hits to be included in the search. It’s used to generate the estimate of total results. Think of it like a hint to search… saying “you only have to work this hard on this query.” Most search implementations only show the next few paging options… they don’t show ALL the options. For instance, if you’re on page 5, your paging control may show the following:
«Previous« 2 3 4 5 6 7 8 »Next»
In this case, you don’t need for search to find ALL the results… you only need it to determine how many more page options you want to show to see if you’re going to show too many or too few. In the above example, you have an additional 3 pages of results you want to show. Continuing on this example, you have a result set of 15 and you have an additional 3 pages you want to show, the Query.TotalResultsExactMinimum property would be 45 as it already is going to factor into the equation the Query.StartRow property:
using (FullTextSqlQuery query = new FullTextSqlQuery(SPContext.Current.Site))
{
query.StartRow = 16;
query.RowLimit = 15;
// TotalRowsExactMinimum = [number of pages to show] * [page size]
query.TotalRowsExactMinimum = 45;
query.QueryText = "SELECT Rank, Title Url FROM Scope() WHERE FREETEXT(defaultproperties,'sales proposal') ORDER BY Rank Desc",
ResultTableCollection results = query.Execute();
ResultTable relevantResults = results[ResultType.RelevantResults];
// do work with the results
}
That’s all there really is to it! One parting word of advice: use the Query.TotalRowsExactMinimum property with care as the higher its set, the greater performance impact there will be on each search query executed.
A special shout out & thanks to Puneet Narula @ Microsoft for helping uncover this very helpful nugget of info.
Andrew Connell (blog)
Microsoft MVP