Use of &sql

CachéQuality release 
1.0.0
Id 
OS0012
Rule type 
Severity 

Major

Major
SQALE characteristic 
  • Efficiency
    • Efficiency compliance
Tags 
performance
Remediation function 
Constant/issue
Remediation cost 
2h

Execution of SQL statements in ObjectScript can be done in one of two ways:

  • using the &sql(...) statement;
  • using the newer SQL classes, such as %SQL.Statement.

The problem with using &sql(...) is that the execution plan will be calculated the first time the query is executed, and never be re-evaluated again.

This can lead to a suboptimal execution plan if the contents of the table(s) change rapidly (volume of data, distribution etc).

It is recommended to use prepared statements instead, which do not have this problem; for instance:

    do ##class(%SQL.Statement).%ExecDirect(/* arguments */)