| Dynamic SQL for Performance? | KEVIN RESCHENBERG
07-25-2007 |
Dynamic SQL in SQR, as I've mentioned in earlier posts (such as here),
can be used to accomplish a number of different goals. Improved performance is not generally
considered one of them. But I ran into an unusual situation in which it actually helped.
Most of the time we code SQL in SQR using variables in this way:
WHERE MY_FIELD = #MyValue During its "compile" phase, SQR hands the SQL to the database
with a placeholder for the value that will be supplied eventually. The database
can check the SQL and develop the appropriate plan for it. At run time, the
value is sent and the prepared SQL is run. This is generally the most efficient method.
With dynamic SQL, we supply parts of the SQL itself—not just the variable values—in
string variables:
WHERE [$NameOfField] = #MyValue This SQL cannot be prepared in advance, since the values are not known until run
time and could even change with each execution of the SQL.
If this type of SQL is run within a loop, it must be scanned and prepared by the database
each time. This results in a performance hit.
I had a program that called several views. These views were a little complex, but they used
an underlying table that had just one numeric key field. When I entered this in our SQL tool,
it ran just fine:
SELECT MY_FIELD FROM PS_MY_VIEW WHERE KEY_FIELD = 12345 The equivalent statement in SQR was in this form:
begin-select
MY_FIELD
FROM PS_MY_VIEW
WHERE KEY_FIELD = #KeyValue
end-select
But that ran very slowly for some reason. Since I knew that the view ran quickly
when given a constant value, I changed the SQR code on a hunch:
let $KeyValue = edit(#KeyValue, '88888')
begin-select
MY_FIELD
FROM PS_MY_VIEW
WHERE KEY_FIELD = [$KeyValue]
end-select
While the original program ran for over 9 minutes, the version as changed
in this way finished in only 1 second! Optimization doesn't get much better than that.
With the dynamic SQL, the database saw the value as a constant. It then apparently chose a different execution plan and was
able to optimize access to the underlying table.
It is important to note that each SQL query was run only once in this program, not multiple times within a loop.
It was definitely an unusual situation, but this is one more thing to try if a program is
performing poorly and nothing else seems to work.
|
