By default, temporary database tables are used to help improve the performance of queries that return medium to large data sets. However, to help performance for the return of smaller data sets, IN Lists are used instead of joins to the temporary database tables. This gives a performance improvement on queries that return smaller data sets that contain fewer rows than the default value. You can change this value in the HybridQueryThreshold property, which sets the limit up to which the IN Lists mechanism is used in queries.
The HybridQueryThreshold property is a parameter in the SPFAppServer.config settings file and is found in each site Settings node in Server Manager. The threshold value is the point at which the site tells the query to use IN Lists to return a smaller data set. This results in the return of fewer objects than the default setting. Above this limit, a query defaults back to using the temporary database tables.
<add key="HybridQueryThreshold" value="500" />
The default setting is 500, and the maximum value is 1000. If you set the value to zero, the IN Lists query mechanism is not used.
-
The HybridQueryThreshold setting should only be modified by administrators with an in-depth knowledge of SmartPlant Foundation database query.
-
The user's Maximum query limit in the Change User Preferences dialog box, which sets the upper limit for the number of items found in a query, forms part of the query decision logic.
For more information, see Change user preferences and Site Settings node properties.