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 clauseContent 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 solutionsPossible 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.