Designing efficient queries

Designing efficient queries is essential to performance: Although the AR System server generates most SQL statements, you can influence the structure of SQL statements by the design of the workflow.

Consider the example in which a user searches for customers by name. Workflow creates the following complex SQL statement:

 
SELECT 
T313.C1,C750010114,C750010112,C750010109,C750010103,C750010113,C750010118,C750010101, 
C750010102,C750010108,C750010106,C750010107,C750010110,C750010111, C750010115,C750010119, 
C750010120 FROM aradmin.T313
WHERE ( 
((T313.C750010114 = ' ') OR (' ' = ' ')) AND 
((T313.C750010109 = ' ') OR (' ' = ' ')) AND 
((T313.C750010112 LIKE ((('DOE' || '%') || 'JANE') || '%')) OR 
('DOE' = ' ') OR ('JANE' = ' ')) AND 
((T313.C750010101 LIKE (' ' || '%')) OR (' ' = ' ')) AND 
((T313.C750010118 = ' ') OR (' ' = ' ')) 
) 
ORDER BY 1 ASC 

This online query takes over 2 seconds. The SQL is overtly complex as it has unnecessary WHERE conditions.

A properly designed workflow creates a SQL statement with WHERE conditions for which the user enters a value.

The following SQL statement has the same functionality as the previous example, but is less complex for the database optimizer to interpret.

 
SELECT 
T313.C1,C750010114,C750010112,C750010109,C750010103,C750010113,C750010118,C750010101,
C750010102,C750010108,C750010106,C750010107,C750010110,C750010111, C750010115,
C750010119,C750010120 FROM aradmin.T313 
WHERE ( 
((T313.C750010112 LIKE ((('DOE' || '%') || 'JANE') || '%')) OR 
('DOE' = ' ') OR ('JANE' = ' ')) 
ORDER BY 1 ASC 

In turn, the database correctly determines the proper index to use and the SQL statement executes in less than 10 milliseconds.