INSERT statements are not cached
Article ID: 127532
Updated On:
Products
CA IDMS
CA IDMS - Database
CA IDMS - ADS
Issue/Introduction
The SQL caching feature of CA-IDMS does not cache INSERT statements. This article describes this situation and a circumvention.
In CA-IDMS, the result of the compilation of dynamic SQL statements can be cached in memory.
This saves the overhead in compiling identical SQL statements every time they are invoked.
For ODBC and JDBC based applications which make heavy use of dynamic SQL, this can result in a significant performance benefit.
This feature does not support INSERT statements.
This is documented in the "Note:" at the top of the documentation page for Dynamic SQL Caching.
In the case of SQL against a network-defined database, if the schema is large, this can have a noticeable impact on performance.
In CA-IDMS, the result of the compilation of dynamic SQL statements can be cached in memory.
This saves the overhead in compiling identical SQL statements every time they are invoked.
For ODBC and JDBC based applications which make heavy use of dynamic SQL, this can result in a significant performance benefit.
This feature does not support INSERT statements.
This is documented in the "Note:" at the top of the documentation page for Dynamic SQL Caching.
In the case of SQL against a network-defined database, if the schema is large, this can have a noticeable impact on performance.
Environment
CA IDMS, all supported releases.
Resolution
An IDEA has been raised on the CA IDMS Communites page to request that INSERT statements be made cacheable.
In the mean time, as a circumvention, it is possible to define an SQL PROCEDURE to perform the INSERT.
This will result in an access module being compiled and the INSERT becomes static, and does not need to be compiled on every invokation.
The SQL PROCEDURE can be invoked with a CALL statement which can be cached.
Sample code to define and invoke the procedure are in the text file attached to this document.
In the mean time, as a circumvention, it is possible to define an SQL PROCEDURE to perform the INSERT.
This will result in an access module being compiled and the INSERT becomes static, and does not need to be compiled on every invokation.
The SQL PROCEDURE can be invoked with a CALL statement which can be cached.
Sample code to define and invoke the procedure are in the text file attached to this document.
Additional Information
Attachments
Feedback
Yes
No
Powered by
