4 Replies - 4993 Views - Last Post: 23 April 2011 - 02:13 AM Rate Topic: -----

#1 Ace26  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 183
  • Joined: 10-August 08

Prepared statements vs. Stored procedures

Posted 22 April 2011 - 06:13 AM

Alright fellas I think I need clarification and answers on the following questions:

1. What is the difference between stored procedures and prepared statements?

2. Does using the two together yield any exponential performance increase as using either impacts performance positively?

3. Which in your (experienced) opinion will improve performance the better (as both do on their own)?

Thanks for your educated responses.

Is This A Good Question/Topic? 0
  • +

Replies To: Prepared statements vs. Stored procedures

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Prepared statements vs. Stored procedures

Posted 22 April 2011 - 03:11 PM

Hey.

#1
The two aren't really comparable, as such. They serve different purposes.

A Prepared Statement is a method of executing SQL queries. Basically, you send the query to the server with placeholders where you want data inserted, and then execute the statement passing the data along. - The benefits to this is that A) You are protected from SQL injection, as the server itself makes sure the data is safe, and B) If you are executing the same query multiple times, you can do so without the server having to receive and parse the query multiple times; it will just be parsed once and then executed as many times as you want. (Which improves performance.)

For instance, using PDO:
<?php
$newAccounts = array(
    array('user1@example.com', 'user1', 'passwdHash'),
    array('user2@example.com', 'user2', 'passwdHash'),
    array('user3@example.com', 'user3', 'passwdHash')
);

$sql = "INSERT INTO accounts(email, name, password) 
        VALUES(:email, :name, :pasword)";
$stmt = $pdo->prepare($sql);

if ($stmt) {
    foreach($newAccounts as $_account)
    {
        $stmt->bindValue(':email', $_account[0], PDO::PARAM_STR);
        $stmt->bindValue(':name', $_account[1], PDO::PARAM_STR);
        $stmt->bindValue(':password', $_account[2], PDO::PARAM_STR);
        if (!$stmt->execute()) {
            trigger_error("Failed to insert account '{$_account[0]}'", E_USER_WARNING);
        }
    }
}
else {
    echo "42";
}
?>




A Stored Procedure, on the other hand, allows you to write code directly on the SQL server. That is, instead of having the front-end (PHP, for example) write code to do a specific task, you can write it on the SQL server as a Stored Procedure, and then PHP can simply call the procedure to perform the task.

For example, instead of writing PHP code to register an account, you could write a procedure that does it for you:
(Rather long example, granted, but I just happened to be working on this at the moment. Not tested though.)
CREATE PROCEDURE `register_account`( 
    IN newEmail VARCHAR(255),
    IN userName VARCHAR(255),
    IN plainTextPass VARCHAR(255),
    OUT error VARCHAR(255)
)
    MODIFIES SQL DATA
BEGIN
    DECLARE passwordHash CHAR(128);
    DECLARE numRowsFound INT DEFAULT 0;
    
    SELECT COUNT(*) FROM `accounts`
    WHERE `email` = newEmail OR `name` = userName
    INTO numRowsFound;
    
    IF numRowsFound = 0 THEN
        SET passwordHash = hash_password(newEmail, plainTextPass);
        
        INSERT INTO `accounts`(`email`, `name`, `password`)
        VALUES (newEmail, userName, passwordHash);
        
        IF ROW_COUNT() = 1 THEN
            SET error = '0';
            SELECT LAST_INSERT_ID() AS 'account_id';
        ELSE
            SET error = '606: Register account INSERT failed.';
        END IF;
    ELSE
        SET error = '605: Duplicate email or name.';
    END IF;
END


And now, instead of having PHP execute all the queries nessecary to verify and create an account, you can just do:
<?php
$stmt = $pdo->prepare("CALL register_account(?, ?, ?, ?)");
if($stmt) {
    $error = -1;
    
    $stmt->bindParam(1, $email, PDO::PARAM_STR);
    $stmt->bindParam(2, $username, PDO::PARAM_STR);
    $stmt->bindParam(3, $password, PDO::PARAM_STR);
    $stmt->bindParam(4, $error, PDO::PARAM_INPUT_OUTPUT);
    
    if ($error === '0') {
        echo "Success!";
    }
    else {
        echo "Error: " . $error;
    }
}
?>



#2
Using prepared statements can increase performance when executing the same statement multiple times, but the performance of a single query execution is likely no different than for an old-school query call.

I'm not sure what performance benefits/drawbacks Stored Procedures have. The benefits of those aren't really in performance, but more in the design/compatibility area. - Having multiple apps implement the same functionality is more likely to bug out than having them execute the same stored procedure. (They'll at least bug out the same way :P)

Using them together won't give you any more or less performance benefits than using them separately. They aren't exactly "connected", if you know what I mean.

#3
That's subjective. Like I say, prepared statements can improve performance, but not always. You'd have to benchmark each situation to find out if it is beneficial.

Edit: Improved the prepared statement code. Was kind of a poor example :P

This post has been edited by Atli: 22 April 2011 - 03:17 PM

Was This Post Helpful? 3
  • +
  • -

#3 Ace26  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 183
  • Joined: 10-August 08

Re: Prepared statements vs. Stored procedures

Posted 22 April 2011 - 03:43 PM

Thanks Atli for your response. But all the cobwebs haven't been cleared out yet. Now I know stored procedures are compiled and stored once for use multiple times and prepared statements know the same fate too. But I still am not clear on one thing:

stored procedures are compiled and stored on the database server for use severally. Are prepared statements stored in the same place too i.e the database server(assuming it is different from the main server machine) or on the server machine where the PHP interpreter and webserver both seat? where?
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Prepared statements vs. Stored procedures

Posted 22 April 2011 - 03:56 PM

That would depend on the SQL server, and the API you use to connect to it.

In the case of MySQL, the server itself is capable of taking the statement and parsing it, and then wait for the client to execute. It also does some behind the scenes caching to improve performance on frequently used statements. - I believe both the PDO and the MySQLi extensions take advantage of this.

In other cases, the front-end API can "emulate" this behavior, by compiling and storing the query on the calling server. However, if you stick to the major players (MySQL, MSSQL, Oracle, Postgres, etc...) you will likely not encounter this.
Was This Post Helpful? 1
  • +
  • -

#5 Ace26  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 183
  • Joined: 10-August 08

Re: Prepared statements vs. Stored procedures

Posted 23 April 2011 - 02:13 AM

Thank very much.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1