Am I right that Prepared Statements are slow?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Am I right that Prepared Statements are slow?

Post by kaisellgren »

Hi,

I have been testing prepared statements on MySQLi - extension.

Code: Select all

echo "MySQLi OO STMT: ";
$c = new mysqli('localhost','root','hottis','test');
$time = time()+microtime(1);
$stmt = $c -> stmt_init();
for ($loop = 0;$loop < 500;$loop++)
 {
  $stmt -> prepare('SELECT username FROM admins WHERE id=1');
  $stmt -> execute();
  $stmt -> store_result();
  $stmt -> bind_result($a);
  $stmt -> fetch();
 }
$stmt -> close();
echo (time()+microtime(1)-$time) .' seconds. Result: ';
echo "$a\n";
That compared to:

Code: Select all

echo "MySQLi P Norm: ";
$c = mysqli_connect('localhost','root','hottis','test') or die('eh');
$time = time()+microtime(1);
for ($loop = 0;$loop < 500;$loop++)
 {
  $r = mysqli_query($c,'SELECT username FROM admins WHERE id=1');
  $a = mysqli_fetch_row($r);
 }
echo (time()+microtime(1)-$time) .' seconds. Result: ';
echo "$a[0]\n";
And the results are:
MySQL P Norm: 0.045994758605957 seconds. Result: admin
MySQLi OO STMT: 0.076005935668945 seconds. Result: admin
I am pretty amazed by these figures... prepared statements are 1.65x slower than normal procedural queries... weren't they supposed to be faster?

Or did I write something wrong?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Am I right that Prepared Statements are slow?

Post by Benjamin »

Where did you get the impression they would be faster?

There's a lot more work involved under the hood when using prepared statements. Compared against executing a raw query with mysql_query, I'm surprised the speed difference isn't more than it is.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Am I right that Prepared Statements are slow?

Post by VladSun »

Maybe because you are doing it in a wrong way. You should prepare the query outside the loop ;)

A prepared query is slower than a single query because you have to talk to the SQL server two times instead of one.
But in your case (multple queries) using a prepared query will be faster for sure.

http://dev.mysql.com/tech-resources/art ... ments.html
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Am I right that Prepared Statements are slow?

Post by kaisellgren »

Where did you get the impression they would be faster?
I have looked into MySQL and PHP source codes and I have a rather good understanding at prepared statements now. From now on, I will use the MySQLi STMT class (not PDO, since it is overly complex to be used for one database vendor) for DML actions that do not yield in a result set. Prepared statements can decrease the time taken to parse queries, so, repetitous queries would be much faster with prepared statements. To be precise, the query does not need to be identical, just the structure of it. For example, running an INSERT query with different data, will get compiled and preparsed, thus, increases the performance. Further more, prior to analyzing and parsing queries, MySQL will always create a so called "plan". This plan is also cached in newer versions of MySQL when prepared statements are being utilized.

It also looks like that when you are not using prepared statements, the protocol actually converts the whole query into a string. This often leads into greater data length, thus, more processing is needed for the conversion plus the data transmission, which also adds up time to the overall network speed. The new protocol, however, sends the queries in their binary format, thus, removing the need for conversions and will also save the time taking for the network transmission.

I would say, use prepared statements, but wisely.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Am I right that Prepared Statements are slow?

Post by VladSun »

Did you try my suggestion? Any results?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Am I right that Prepared Statements are slow?

Post by kaisellgren »

VladSun wrote:Did you try my suggestion? Any results?
SELECT TEST

MySQL P Norm: 0.060736179351806640625 seconds. Result: admin
MySQLi P Norm: 0.0474643707275390625 seconds. Result: admin
MySQLi OO Norm SSL: 0.0498027801513671875 seconds. Result: admin
MySQLi OO Norm: 0.04782962799072265625 seconds. Result: admin
MySQLi P STMT: 0.04024791717529296875 seconds. Result: admin - prepare outside loop
MySQLi OO STMT: 0.08924579620361328125 seconds. Result: admin - prepare inside loop

INSERT TEST

MySQLi P Norm: 6.06797122955322265625 seconds.
MySQLi OO STMT: 5.752935123443603515625 seconds.

CLEAN UP... DONE
My tests have been rather interesting. MySQL extension is noticably slower than MySQLi. Also, there does not seem to be any difference between OO or P. I run the tests 10 times, and OO vs P ended up in a 5-5 situation. More over, statements are faster to fetch data when you have the preparement outside the loop. Otherwise, they are just slower.

One interesting thing was that using a secure socket layer barely added any burden to the processor. It was way less than using a MySQL extension instead of MySQLi.

When it comes INSERTion, prepared statements are faster, but nothing too great.

So would I use prepared statements? That depends entirely on the situation ;) - if there are several repetitous SQL calls, I could use prepared statements, but with normal queries, I will not use them.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Am I right that Prepared Statements are slow?

Post by VladSun »

kaisellgren wrote:More over, statements are faster to fetch data when you have the preparement outside the loop. Otherwise, they are just slower.
Simply, they must not be used in the loop - that's why you "prepare" them :)
kaisellgren wrote:So would I use prepared statements? That depends entirely on the situation ;) - if there are several repetitious SQL calls, I could use prepared statements, but with normal queries, I will not use them.
Yes, the major purpose of using prepared queries is to increase the performance with repetitous (parameterized) queries.
Otherwise... there are people that would say that security is better - no SQL injections. I tend to agree with this, while I rarely use them for such purpose ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Am I right that Prepared Statements are slow?

Post by kaisellgren »

You can make your own prepared query system if you want the security aspect of it.
VladSun wrote:
kaisellgren wrote:More over, statements are faster to fetch data when you have the preparement outside the loop. Otherwise, they are just slower.
Simply, they must not be used in the loop - that's why you "prepare" them :)
Yes, I know, but think about if you have an app with 100 different queries, you must prepared them all - so, the test was to test whether prepared statements were any good for such situations. Maybe the test was a bit unclear. Of course you do not prepare the same query multiple times :P
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Am I right that Prepared Statements are slow?

Post by kaisellgren »

I just noticed how complex it is to support prepared statements and own prepared statements in one wrapper class.

Code: Select all

 public function prepare($statement,$preparse = false)
   {
    if ($preparse == false)
     {
      $this -> statement = $statement;
     }
    else
     {
      if ($this -> is_connected === false)
       $this -> connect();
      $this -> statement = mysqli_stmt_init($this -> connection_id);
      mysqli_stmt_prepare($this -> statement,$statement);
     }
   }
My prepare() method basically accepts preparse parameter, which turns on the internal PS system. Otherwise, I will use my own PS. The problem really comes at the point when I start to implement a caching system. The built-in PS requires the connection while my own does not. This adds some slight complexity in the whole system. For instance, the cached results should be retrieved prior to the preparation... but I cannot know what to fetch from the cache since the statement is just a preparation and does not contain the whole SQL query.

Any ideas? Should I just disable caching for prepared PS'es ? I makes little point of having caches if I have to establish a DB connection and talk to it...
Post Reply