SmartPlant Foundation now uses IN Lists (conditions with expression lists) to return a small data set from a site.
Unlike queries using temporary datasets, which are better suited to returning medium to large data sets, IN Lists execute the return of smaller result sets faster using less I/O on the database than queries using temporary database tables.
The following examples show temporary table joins compared to IN Lists.
|
Temporary Table Joins |
IN Lists |
|---|---|
|
SELECT * FROM DATAOBJ o WHERE o.OBID IN (SELECT OBID FROM GRAPH_TMP WHERE SRCEDGE = '6bbbdb11-4be0'); |
SELECT * FROM DATAOBJ o WHERE o.OBID IN ('2FK000A','2FK001B','2FK002C'…); |
|
SELECT DISTINCT(OBJDOMAIN) FROM GRAPH_TMP WHERE SRCEDGE = '6bbbdb11-4be0'; |
SELECT DISTINCT(DOMAINUID) FROM DATAOBJ WHERE o.OBID IN ('2FK000A','2FK001B','2FK002C'…); |
To support both query mechanisms in SmartPlant Foundation, the Data Access Layer (DAL) in SPF.Common has been re-engineered and the following components have been changed:
-
SPF.CacheService
-
SPF.Server
-
SPF.Service
Any modules that depend on these components must be modified to factor in the new
changes.
Object and relationship inflation redesign
Inflation takes query results from one table, retrieves related interface and property data from other tables, and returns an aggregated data set. Object inflation and relationship inflation have been redesigned in the DAL, as follows:
-
Query methods in the DAL have been changed to use the new code, so that they can benefit from any performance improvements. For example, the QueryObjects method in QueryCriteria has been changed.
-
The IObjInflationClass interface definition and its children have been redesigned. In the previous DAL code, the IObjInflationClass interface definition was the root interface used for the inflation class hierarchy.
-
Object inflation and relationship inflation have been separated in the new hierarchy. Previously, they were conflated in the old hierarchy, as all relationship inflators were also object inflators.
-
Inflation is now modelled with the new IObjInflator and IRelInflator interface definitions to return a data set.
-
The new IPostProcessor interface definition now takes the results of a query or expansion and executes further SQL. The IPostProcessor interface definition has a generic return type, which can return any type of data, depending on the query. For example, if a count is required a data set can be returned, or if a list of configurations is required a list of strings can be returned.
-
Relationship expansion and edge expansion behavior has not changed, except in their use of inflation classes for processing results. The existing behavior has not changed, as relationship expansion still relies on temporary database tables.
For examples, see Calling with custom SQL statements. For more information on relationship expansion, see Examples of relationship expansion behavior.
Temp table IDs and IN Lists
The results of queries and expansions are now inserted into a temporary database table, but they are hidden behind a layer of abstraction. This avoids the temporary database tables and uses the IResultProvider interface definition to avert any complication.
To help separate the concerns of object inflation and relationship inflation, the results providers have been separated into the IObjResultsProviders and IRelResultsProviders interface definitions, although their implementation is typically common. Results providers provide OBID criteria, which uniquely identify some results in the database. OBID criteria can be inserted into SQL statements, as in the following example:
SELECT … FROM … WHERE OBJOBID IN (OBID criteria)
There are two types of OBID criteria:
-
One-shot criteria should only be used when the results of a query are used exactly once, such as in a straightforward count. They do not guarantee use of temporary database tables or IN lists, and may be complex and involve costly joins and conditions.
-
Reusable criteria always makes use of temporary database tables or IN lists. They should be used when the results of a query are used more than once, such as for inflation.
Behind the scenes the DAL supplies different implementations of IResultsProvider
interface definition, depending on whether or not it is possible to use IN Lists instead
of temporary database table joins.
HybridQueryThreshold property in SPFAppSever.Config setting
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.