Optimize SQL databases – Stored procedures

2011-03-05

Stored procedures is code in a database similar to a function. Since the code is entered in the database in advance, it can be optimized somewhat before being executed because it does not need to be interpreted on every query. This has several advantages:

A smaller amount of data must be sent to the database when a query is made.

You can, in the same way as in a trigger, log transactions in a stored procedure.

A stored procedure will give you an extra layer of security on the database. Before a transaction is done, you can check the included data to see that it has the right type, length, etc. In the code shown later in this article, there are examples of several different types of operations that can be used in a stored procedure. A disadvantage is that the way stored procedures is written differ between implementations of SQL. The example later, however, is written for MySQL.

The logic can be centralized in the database rather than keeping it in the application. There can also be a security risk if you let the application control the queries.

The following code shows an example of a stored procedure that performs several different operations:

-- An employee identification consists of the first letter of their name followed by a sequential number.
-- Receives first name, last name and salary as an argument.
CREATE PROCEDURE New_Employee(new_first_name varchar(20), new_last_name varchar(20), new_salary int)
BEGIN
-- Starting the employee ID number at 0.
SET @NR = 0;
-- Get the first letter of the name of the employee.
SET @CH = (SELECT UPPER(SUBSTR(new_first_name, 1, 1)));
-- If several employees have the same initial letter, the maximum sequential number of the letter is retrieved.
IF EXISTS (SELECT nr FROM Employees WHERE name = @CH) THEN
SET @NR = (SELECT MAX(nr) AS max_nr FROM Employees WHERE name = @CH);
END IF;
-- Adjusts the minumum and maximum salary.
IF (new_salary < 12000) THEN SET new_salary = 12000; END IF;
IF (new_salary > 35000) THEN SET new_salary = 35000; END IF;
-- Add a new employee with the next ID number.
IF (NR < 99) THEN
INSERT INTO Employees VALUES (@CH, @NR + 1, new_salary, new_first_name, new_last_name);
END IF;
END;

Stored procedures and function are similar in many ways. Functions are forced to return a value when it is optional for a stored procedure. A function can also only return one value while a stored procedure can return several values if needed. They return values with the use of the RETURN keyword. Functions can also be used in SELECT operations as long as they don’t manipulate any data. Stored procedures can not be used in a SELECT operation, even if it has a RETURN statement.

Both functions and stored procedures can receive multiple parameters. you specify them as a comma separated list in the function or procedure call. They are written in the same way as columns are written in the creation of a table. Exemple: “name varchar(20), age int, …“. A stored procedure is called in the following way: