DB2 query optimization for subtree searches

Edit online

If a directory server hierarchy is deep nested, the subtree search might take considerable time to return the results. To retrieve subtree search results faster, you must optimize the SQL queries that access the LDAP_DESC table and other tables.

To optimize SQL queries for improved search performance, the DB2® optimizer analyzes the distribution statistics and creates a data access plan for each search. You can use environment variables to influence the data access plan or the query access plan that the DB2 optimizer generates. You must run the idsrunstats command against the directory server instance.

Environment variables

To influence the data access plan, use the following variables:

LDAP_MAXCARD

Use the LDAP_MAXCARD environment variable to set the cardinality value.

If you set cardinality, the attribute indexes are resolved first and the LDAP_DESC index is considered towards end for query evaluation. The following search performance might be observed:

  • Improvement in subtree search performance against large subtrees with many descendants.
  • The disadvantage is that all subtree searches are treated as search against a large subtree. Therefore, you might observe a performance degradation if the subtree search is against a small subtree with fewer descendants.
IBMSLAPD_USE_SELECTIVITY

Use the IBMSLAPD_USE_SELECTIVITY variable to set DB2 selectivity.

If you set DB2 selectivity, the following performance might be observed:

  • Improvement in subtree search performance against top 10 subtrees that contains the most descendants. For top 10 subtrees with most descendants, the attributes filters are resolved first and the LDAP_DESC table is considered towards end for query evaluation.
  • If a subtree search is run against a subtree that is not in top 10 with most descendants, you might observe a performance degrade. For the subtrees that are not in the top 10 with most descendants, the LDAP_DESC index is resolved first followed by the attribute indexes.

Query access plan

The LDAP_MAXCARD and IBMSLAPD_USE_SELECTIVITY environment variables are ways to improve subtree search performance in specific cases. In certain subtree search scenarios, even if you set the environment variables you might not obtain the required performance improvements. Instead of using the environment variables, use the query access plan that the DB2 optimizer generates. IBM® Security Verify Directory, version 6.3.1 or later, provides an option to use the DB2 optimizer to improve the subtree search performance in all subtree search scenarios.

When you run a subtree search against a directory server, the following process occurs:

  1. The LDAP search query is converted to a series of SQL queries that evaluate the LDAP_DESC index and attribute indexes.
  2. The SQL query that contains the LDAP_DESC index is evaluated by using a parameterized value for an ancestor EID (AEID). In IBM Security Verify Directory, version 6.3.1 and later, the parameterized value for AEID is replaced with the evaluated AEID literal value for further processing of the SQL queries.
  3. The DB2 optimizer uses the AEID literal value to identify whether the subtree is large or small. The DB2 optimizer uses the collected distribution statistics to decide whether to resolve the attribute indexes or the LDAP_DESC index first in its data access plan.
  4. Depending on the type of subtree and filter, the DB2 optimizer creates an optimal data access plan for subtree searches against both large and small subtrees.