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
Directory Server,
version 6.3.1, 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:
- The LDAP search query is converted to a series of SQL queries
that evaluate the LDAP_DESC index and attribute indexes.
- The SQL query that contains the LDAP_DESC index
is evaluated by using a parameterized value for an ancestor EID (AEID).
In IBM Security
Directory Server,
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.
- 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.
- 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.