Use of &sql
- Efficiency compliance
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 */)