MySQL Stored Procedure Programming

Name

SQL-08: Avoid including SQL in functions that may be used in SQL

Synopsis

You are free to include SQL statements within stored functions (with the exception of SQL statements that return result sets to the calling program). You should, however, be very wary of doing so if you think that your stored function might itself be called inside a SQL statement.

When you use a function that contains SQL in a SQL statement, you are effectively “nesting” two SQL statements. For every row returned by the “outer” SQL, you will have to execute the “inner” SQL. Such nested SQL statements can exhibit extremely unpredictable or undesirable performance.

For instance, consider the simple stored function below:

    CREATE FUNCTION cust_contact_name (in_customer_id INT)
      RETURNS VARCHAR(100)
      READS SQL DATA
    BEGIN
      DECLARE  v_contact_name VARCHAR(100);

      SELECT CONCAT(contact_firstname,' ',contact_surname)
        INTO  v_contact_name
        FROM customers
       WHERE customer_id=in_customer_id ;

      RETURN( v_contact_name);

    END$

It contains an efficient query, but nevertheless, if we include it in a query against the customers table as follows:

    SELECT cust_contact_name(customer_id) FROM customers

our execution time is about five times greater than if we performed the same operation within the SQL itself:

    SELECT CONCAT(contact_firstname,' ', contact_surname) FROM customers

The situation becomes even worse if the SQL inside the function is not completely optimized. In Chapter 10 we provide an example in which the use of a stored function ...