One of the easiest tricks to improve performance of some App Engines is the ‘ReUse’ setting. It’s useful when you have a SQL object that is called multiple times, for instance when you have a DoSelect that runs a SQL Step repeatedly, substituting different Bind variables in each time.
The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.
In addition to the performance gain from the above, some databases also have an area of memory cache where recently used SQL is stored. Oracle definitely has this cache, I’m not sure about other databases. As each SQL statement would contain Binds rather than literals it would be an exact match for the previous SQL Statement enabling the database to use the cached SQL and thus giving a further performance boost.
As always, there are some exceptions. If the %Bind is either part of either a record or column name (using ‘NOQUOTES’) then it shouldn’t be used. If this is the case the record and/or field is changing each time and as a result a new plan would be needed, therefore PeopleSoft converts the Binds to statics.
The performance gains that this gives will vary depending upon your SQL and the data, but a recent opportunity I had to set the ReUse flag to Yes dropped processing time for an AE Section from 15 mins to just under 2. Well worth considering next time you write an App Engine.
The default setting for ReUse is ‘No’, which means that each time the SQL Step is called it is recompiled and new execution plan generated, bad news if the SQL has to run a large number of times. If the ReUse setting is ‘Yes’ then any %Binds in the SQL statement are converted into ‘:1’, ‘:2’ etc. This means the statement can just be compiled once and the same plan used each time, resulting in really quite dramatic improvements in processing time.
In addition to the performance gain from the above, some databases also have an area of memory cache where recently used SQL is stored. Oracle definitely has this cache, I’m not sure about other databases. As each SQL statement would contain Binds rather than literals it would be an exact match for the previous SQL Statement enabling the database to use the cached SQL and thus giving a further performance boost.
As always, there are some exceptions. If the %Bind is either part of either a record or column name (using ‘NOQUOTES’) then it shouldn’t be used. If this is the case the record and/or field is changing each time and as a result a new plan would be needed, therefore PeopleSoft converts the Binds to statics.
The performance gains that this gives will vary depending upon your SQL and the data, but a recent opportunity I had to set the ReUse flag to Yes dropped processing time for an AE Section from 15 mins to just under 2. Well worth considering next time you write an App Engine.
0 comments:
Post a Comment