July 25, 2007

Dynamic SQL for Performance?

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.

Until next time...