IBM FileNet P8, Version 5.2.1            

SQL tuning: Property range search with a different ORDER BY property (continuable query)

A continuable query might be ordered by a different property from the one that the range search conditions reference. To remedy slow query execution, change the ORDER BY clause of the query to reference the same property.

For example, the property in the ORDER BY clause of the following query is different than the property in the range search condition:
Table 1. Query with ORDER BY clause example
Content Platform Engine SQL Corresponding database SQL (continuable query)
SELECT Id 
FROM Document 
WHERE Property1 > 'aValue' 
AND Property1 < 'zValue' 
ORDER BY Property2 
SELECT object_id, object_class_id, security_id, ... 
FROM DocVersion 
WHERE home_id Is Null 
AND uxy_Property1 > ? 
AND uxy_Property1 < ? 
ORDER BY uxy_Property2, object_id 

If the query returns many rows, query execution might be slow regardless of any database indexes. Because an index look-up on uxy_Property1 returns rows in Property1 order and not Property2 order, the query might have to perform a sort. The query execution plan might not use any index or might scan the entire index on Property2 if one is present.

A solution for slow query execution is only possible if the ORDER BY clause is first changed to use Property1 as shown in the following example:
SELECT Id From Document 
WHERE Property1 > 'aValue' 
AND Property1 < 'zValue' 
ORDER BY Property1 


Last updated: March 2016
p8ppt272.htm

© Copyright IBM Corporation 2016.