IBM FileNet P8, Version 5.2.1            

SQL tuning: No search conditions

Adding a search condition to a query that has no WHERE clause can improve query performance.

For example, the following query has no WHERE clause:
Table 1. Query with no Where clause
Content Platform Engine SQL Corresponding database SQL (continuable query)
SELECT Id 
FROM DocClass
SELECT object_id, object_class_id, security_id, creator, ... 
FROM DocVersion 
WHERE object_class_id 
IN (?) 
AND home_id Is Null 
ORDER BY object_id 
If possible, add a search condition to the query as shown in the following table:
Table 2. Query solutions
Possible solution Example
Add a search condition on an indexed property
SELECT Id 
FROM DocClass 
WHERE Property1 = 'value'
Add a search condition on a property for which only instances of DocClass have a value
SELECT Id 
FROM DocClass 
WHERE Property1 Is Not Null
Restriction: The performance of queries without WHERE clauses can be problematic because of the database SQL that is generated for the query and property skew: the cardinality of object_class_id is typically too low to result in an index on that column to be used in a query execution plan. Therefore, a search against some small minority of rows results in a table scan. Solutions to skew exist that would allow an index on object_class_id to be used to help the search. For more information, see Property skew resolution.


Last updated: October 2015
p8ppt271.htm

© Copyright IBM Corporation 2015.