Optimizing query execution

There are several items you must consider when using prepared SQL queries.

Consider the following when using prepared SQL queries:
  • SQLExecDirect is optimized for a single execution of an SQL statement. Thus, it is used for SQL queries that are not executed repeatedly.
  • In cases where SQL queries are executed multiple times, using SQLPrepare and SQLExecute improves performance. Typically, you can do this with input and output parameters.
  • SPL routines can be called from an ODBC application to perform certain SQL tasks and to expand what you can accomplish with SQL alone. Because SPL is native to the database and SPL routines are parsed and optimized at creation, rather than at runtime, SPL routines can improve performance for some tasks. SPL routines can also reduce traffic between a client application and the database server and reduce program complexity.
  • When a stored procedure with a return value is executed using the HCL OneDB™ ODBC Driver, errors returned by the procedure are not returned to the application until a fetch is called on the result set. Error information from stored procedures with no returned values is available immediately following the execution of the procedure.