Improving Query Performance
Latency in query execution is the amount of time it takes to execute the query and get back a result set. The following table shows some factors that affect query latency.
| Factor | Effect on Query Latency |
|---|---|
| Size of corpus. | The number of documents in the document library and external sources incorporated into the workspace catalog, and content from Dedicated Content Indexes (DCIs) can increase query latency. |
| The search terms used. | Searches over rare terms are faster than those performed over commonly occurring terms. It is common for Internet sites to include large documents containing frequently used keywords. One way to improve performance is to determine such files and use site path rules that exclude these files from being crawled. |
| The query predicates used. | Choice of full-text predicates can increase query latency. For more information about query predicates influence query performance, see Query Predicates and Performance. |
| The query traversal depth used. | Deep query traversal can increase query latency. For more information about how traversal affects query execution, see Search Query Path. |
| Querying over property values of metadata. | Property value queries over metadata can increase query latency because the value for the property must be read from the property store to confirm that it is an exact match. |
| Managing resource limitations. | Query latency improves if components of the Search service do not compete for resources on the same SharePoint Portal Server computer. For more information about configuring a server dedicated to indexing, see the "Client, Server, and Network Planning," "Server Planning," "Server Configurations," "Multiple Server Scenarios" sections in SharePoint Portal Server Planning and Installation. |
| Limiting result set size. | Specifying a limit on the maximum number of rows returned by the Search engine can significantly improve query latency. For more information about this, see Limiting Result Set Size. |
| Managing query timeouts. | Specifying a limit on how long to wait before a query is timed-out can improve query latency. For information on configuring query time-outs, see the "Managing Workspaces", "Manage User Access to a Workspace", "Specify the Query Time-Out" sections in SharePoint Portal Server Administrator's Guide. |
| Setting ORDER BY clause. | Ordering queries by rank does not impact query latency because rank is calculated at query time by the Search engine. Ordering queries by anything else causes the query to access the property store to retrieve that value. |
| Checking equality through regular expressions. | Using a regular expression, such as in a non-full-text predicate, to check equality will increase query latency. |
Several performance monitor counters are provided to monitor searching on the dashboard site.
You can use the Microsoft Search Catalogs object to monitor the number of and/or the rate of the following for each workspace: failed queries, queries (all), results and successful queries. For more information about performance monitors, see SharePoint Portal Server Administrator's Guide.
Configuring and Caching the Property Store
Queries will perform best when a high a proportion of the property store is loaded into memory. There are several ways to achieve this.
The SharePoint Portal Server Search service has registry settings that place limits on the size of the in-memory cache of the property store. You can increase the size of this cache by setting the following two registry keys:
Minimum cache size
\HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\Databases\CacheSizeMin
Maximum cache size
\HKEY_LOCAL_MACHINE\Software\Microsoft\Search\1.0\Databases\CacheSizeMax
The value for the key is the number of 4K pages of memory, represented in hexadecimal, which will be allocated for the cache. On a system with 2 gigabytes of RAM, setting the minimum cache size to 40000 and the maximum as 48000 will allow for a cache that is between 1 and 1.2 gigabytes of the RAM.
Note
You will have to restart the Search service for the registry settings to take effect.
Warning
- Setting the difference between minimum and maximum cache size to be large may degrade performance.
- Setting the maximum cache size to a large percentage of the available RAM will degrade performance.
When the system starts up, run a query that selects properties so that they are cached into memory. After the scripts finish executing, properties loaded from the cache will remain in memory until the SharePoint Portal Server computer is rebooted or the Search service is restarted.
The Search property store is cached into memory as a side-effect of propagating an index. For more information on how to propagate an index, see the "Managing Indexes," "Propagating an Index" sections in SharePoint Portal Server Administrator's Guide.
Limiting Result Set Size
You can specify the maximum number of rows returned by a search query submitted through WebDAV request by setting the MS-SEARCH-MAXROWS header. Setting the header to return 200 rows or fewer improves query latency.
Note
The value should not exceed 2000.
For more information about using this and other WebDAV features, see WebDAV/DASL Request and Response Syntax.
Query Predicates and Performance
You can use the CONTAINS predicate to search for exact strings of words or phrases. A query for "SharePoint Portal Server" returns only exact matches on the phrase. In a FREETEXT query on the same search terms, the words are searched individually as well as the phrase. For example, the query on "SharePoint Portal Server" matches documents that contain "SharePoint" OR "Portal" OR "Server". FREETEXT uses the thesaurus and also invokes a stemmer on the query text. The result is that the query is run for all permutations of the inflected forms for individual words and phrases in the query text. FREETEXT also uses probabilistic ranking algorithm, whereas CONTAINS does not. The documents returned by the FREETEXT query have more hits than CONTAINS and may take longer to process