Improving performance of procedures and functions

 

When creating a stored procedure or a user-defined function (UDF), the SQL procedural language processor does not always generate the most efficient code. However, you can reduce the number of database engine calls and improve performance.

Some changes are in the design of a routine and some are in the implementation. For example, differences between how the C language compiler handles host variables and the way the SQL procedural processor requires the host variables to be handled can cause many calls to the database engine. These calls are very expensive and, when done many times, can significantly degrade performance.

• Improving implementation of procedures and functions
  These coding techniques help reduce the processing time of a function or procedure.

• Redesigning routines for performance
  Even if you follow all of the implementation tips, a procedure or function still might not perform well. In this case, look at the design of the procedure or user-defined function (UDF) and see whether there are any changes that can be made to improve the performance.

 

Parent topic:

Routines