Determining when to use SQL routines or external routines

When implementing routine logic you can choose to implement SQL routines or external routines. There are reasons for choosing each of these two implementations.

About this task

As long as SQL routines can meet your requirements, you should choose SQL routines over external routines. Use external routines when you must implement complex logic, or access files or scripts on a database server.

Procedure

  • Choose to implement SQL routines if:
    • SQL PL and SQL statements provide adequate support to implement the logic that you require.
    • The routine logic consists primarily of SQL statements that query or modify data and performance is a concern. Logic that contains a relatively small amount of control-flow logic relative to the number of SQL statements that query or modify database data will generally perform better with an SQL routine implementation. SQL PL is intended to be used for implementing procedural logic around database operations and not primarily for programming complex logic.
    • The SQL statements that you need to execute can be executed in an external routine implementation.
    • You want to make the modules highly portable between operating system environments and minimize the dependency on programming language code compilers and script interpreters.
    • You want to implement the logic quickly and easily using a high level programming language.
    • You are more comfortable working with SQL than with scripting or programming languages.
    • You want to secure the logic within the database management system.
    • You want to minimize routine maintenance and routine package maintenance upon release upgrades or operating system upgrades.
    • You want to minimize the amount of code required to implement the logic.
    • You want to maximize the safety of the code that is implemented by minimizing the risk of memory management, pointer manipulation, or other common programming pitfalls.
    • You want to benefit from special SQL caching support made available when SQL PL is used.
  • Choose to implement an external procedure if:
    • If the routine logic is very complex and consists of few SQL statements and routine performance is a concern. Logic such as a complex math algorithm, that involves a large amount of string manipulation, or that does not access the database will generally perform better with an external routine implementation.
    • If the SQL statements that you need to execute can be executed in an external routine implementation.
    • The routine logic will make operating system calls - this can only be done with external routines.
    • The routine logic must read from or write to files - this can only be done with external routines.
    • Write to the server file system. Do this only with caution.
    • Invoke an application or script that resides on the database server.
    • Issue particular SQL statements that are not supported in SQL procedures.
    • You are more comfortable programming in a programming language other than SQL PL.