Select Fields in Well-structured QueriesQueries that perform well always include at least 1 field that can be converted to SQL to limit the results returned from the database server. Criteria that cannot be converted to SQL are evaluated against this candidate result set in memory and the final result set is displayed. The smaller the candidate result set, the better. Well-structured queries use several criteria that are “SQL convertible” so that the work performed in memory is kept to a minimum. The only query terms considered for SQL conversion are relational expressions that use this syntax: SQL convertible fields were formerly referred to as “indexed” fields, before the advent of indexed attributes. The query optimizer processes a query in the following manner:
Steps 4-6 are repeated for each vault identified by the Vault field of the query. The most optimal queries use sufficiently specific SQL convertible terms to result in the smallest possible candidate result set. This strategy provides the following advantages:
The following selectables are SQL convertible. All queries should include at least one of these criteria:
You can use SQL convertible fields with any relational operator (==, !~=, <> and so on) applied to a constant value that can include wildcards. For example, name ~= 'A*B' Returns all objects whose name begins with “A” and ends with “B”. Use of select fields in where clauses that are not in the list above will likely result in non-optimal queries since non-SQL convertible fields have to be evaluated on the client. It is best not to use any word that can be a selectable for a business object or connection as a value in the Where clause of a query because it will be evaluated as a select clause rather than being taken literally. If it is unavoidable, see Using Const for Reserved Words. Clauses That Are Not SQL convertibleAny selectables not on the above list are not SQL convertible when used in a where clause. For example, the following are not SQL convertible: description grantor (and grantee, granteesignature) state[] revisions[] previous (and next) type.kindof “Revision” is not SQL convertible when included in the where clause, but is SQL convertible when included in the business object specification part of the query. Selectables that are not SQL convertible cannot be built into the SQL commands that get objects and connections from the database server, so these clauses are not used to limit the number of objects that are retrieved from the server. Qualifying these clauses is very sensitive to the number of objects retrieved, since for each such object, additional SQL calls have to be constructed to retrieve the values of these fields, and data has to be stored in the clients memory. Given these realities:
Here are a few examples of common queries that are bound to be slow. In all of the examples, the type (PART) is specified, but a production database can have huge numbers of PARTs, so the type specification is not very limiting: PART * * where 'description ~~ “*a word*”' PART * * where 'state[StateName].satisfied == TRUE' PART * * where 'revision == last' In the above queries, it would be best to include an attribute that substantially limits the number of PARTs that the system would have to examine. The number of objects retrieved from the database server (that is, the size of the candidate result set) by the SQL convertible fields is the biggest factor in query performance and client memory requirements. The length (in characters) of the where clause or number of expressions within the where clause are immaterial. Actually, a longer where clause is desirable if much of it is comprised of SQL convertible fields that limit the number of objects retrieved: PART * * where ' (description ~~ “*a word*”) AND (attribute[Keyword] ~~ “abc*”) AND (attribute[Keyword] ~~ “123*”) ' | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||