MySQL – Stored Procedure Vs Prepared Statement

Udemy Editor

This article will consider stored procedure and prepared statements from a MySQL perspective. It will examine each in turn and discuss their similarities and their differences, their individual strengths and weaknesses, what particular problems do they solve and how can they be best utilized?

If you would like an introduction to MySQL, take a course at Udemy.com

MySQL Stored Procedures

A stored procedure (SP) is a server-side procedure written in SQL, which is stored in the database and is directly accessible by the database engine. It is also exposed to client applications via programming language connections. Server-side stored procedures offer many advantages over typical client side logic processing. The obvious one is the reduced traffic that travels over the network, which is the main latency inducing bottleneck regards application performance – or at least the perception of performance.

Another performance issue with client side processing is they still need to send full textual queries to the database. The DB drivers on the server-side still have to parse, analyze, plan, and optimize every time the query statement is called. It is far more efficient to have the procedure stored on the server, where the DB engine can pre-compile and analyze the procedure once, then commit it to cache and run it directly.

A third advantage is the “write once and execute everywhere” principle. A programmer can write in SQL and store it on the database. Then, it can be available and called by any connected client programming language. This makes development and maintenance more streamlined and efficient, as there is only one shared procedure to write and troubleshoot.

Last but not least, a main reason to use stored procedures is database security. Stored procedures provide added security by decoupling the user’s access to the database. For instance, there is no requirement to grant access or privileges other than the execute privilege to run the stored procedure.

Stored procedures are written server side using SQL an example is shown below.

DELIMITER $$
CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;
 
END$$

To learn more about MySQL and stored procedures, take a course at Udemy.com

Prepared Statements

Prepared statements (PS) are often confused with stored procedures perhaps because they have the same core advantages. Prepared statements like stored procedures improve in most cases performance and efficiency. They also provide increased database security as they decouple the application logic from the data. However, they are very different and though their purposes may be similar, their methods and mechanics are not. So what is a prepared statement?

MySQL versions 4.1 and later support server-sided prepared statements, which utilize the enhanced binary client-server protocol. The binary protocol is much more efficient when transporting client-server traffic than the textual format protocol. This improves network performance as binary data has smaller byte size than ASCII text.

A prepared statement consists of three elements;

prepare – prepares a statement for execution by giving it a name and defines the SQL query to perform
execute – executes the prepared statement and passes the variables via placeholders
De-allocate – releases a prepared statement and resources

The basic syntax for a prepared statement is shown below

PREPARE  id  FROM 'SELECT customer_id, name
FROM customers
WHERE customer_id = ?';
SET @pc = '107';
EXECUTE id USING @pc;
DEALLOCATE PREPARE id;

The code above initially prepares the statement for execution by giving it a name (id).
It then defines the SELECT statement. The thing to note here is the
‘?” which is used as a place holder for the name expression.
Next, the variable is defined for the name: SET @pc = ‘107’;
Then the query is executed using the name variable @pc
Finally de-allocate prepare and free resources

Once the prepared statement has been created, the client sends the server a prototype of the query with a placeholder for the variable. The server then parses, analyses, and processes this skeleton query. This is the processing and preparation burden, which the server must go through with every SQL query. However, with prepared statements the server stores the partial structure representing the query and returns to the client a statement handle representing this partially processed query. The client can then rerun the query repeatedly by sending the server only the statement handle and placeholder data.

This means that only on the first occasion during a session does the client need to send the server the full body of the SQL query and only on that occasion does the server need to parse, analyze and plan for execution of the statement. Subsequent times the client only sends the handle and variables, greatly improving the network performance and efficiency. Likewise, the server only requires the query to process the handle and the variable and all the preparation work has already been cached.

One caveat though when working with prepared statements; they are only available on the local connection during a session. Once the session is closed, the prepared statement will be torn down and de-allocated, the session persists and can mitigate this problem to an extent. However, there is another way to make prepared statements accessible globally.

Prepared statements do not have to use the client libraries such as PHP or Java. Instead, they can use the SQL interface. The case for using a SQL interface is that MySQL 5 and later can use the prepared statement on the server-side inside a stored procedure.

Here is an example:

DROP PROCEDURE IF EXISTS `table`.`get_cust`;
 DELIMITER \\
 CREATE PROCEDURE `get_cust’
 BEGIN
 SET @cid=input;
 prepare id from
 'SELECT customer_id, name
                    FROM customers
                    WHERE customer_id = ?';
 
 execute id USING @cid;
 DEALLOCATE PREPARE id;
END\\

The prepared statement nested within the stored procedure is marked in bold.

So why use MySQL prepared statements?

For performance and efficiency
For security – decouples logic from data, and enforces data-type rules
For creating dynamic SQL within stored procedures

MySQL prepared statement provide a way to write efficient queries from client programming languages such as VB, PHP and Java which utilize MySQL enhanced binary protocol. Alternatively, programmers could choose the SQL interface and nest their prepared statements within stored procedures.

Learn more about MYSQL advanced topics at Udemy.com