This documentation supports the 9.1 version of BMC Remedy ITSM Deployment.

To view the latest version, select the version from the Product version menu.



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.

Was this page helpful? Yes No Submitting... Thank you

Comments