CODEWITHSUNDEEP
phppdo
Rollor
Rollor

Reputation: 621

Can I pdo prepare ALL my sql queries in one php file?

Let's say I make the file below:

database.php

$db = new PDO("mysql:host=localhost; charset=utf8; dbname=myDb, username; password");

$sqlMyQuery1 = $db->prepare('SELECT * FROM tblTable1 WHERE Column1 = :Column1');
$sqlMyQuery1Param = array(':Column1' => $_POST[FormItem1]);

$sqlMyQuery2 = $db->prepare('SELECT * FROM tblTable2 WHERE Column1 = :Column1');
$sqlMyQuery2Param = array(':Column1' => $_POST[FormItem2]);

$sqlMyQuery3 = $db->prepare('SELECT * FROM tblTable3 WHERE Column1 = :Column1');
$sqlMyQuery3Param = array(':Column1' => $_POST[FormItem3]);

I then include this file to my other php files, so I can just do this on the fly:

$sqlMyQuery1 -> execute($sqlMyQuery1Param);

Given that in a real wesite, I'll have TONS of queries, will it affect the performance of my website, because I am "preparing" a bunch of queries everytime the page loads?

Upvotes: 0

Views: 130

Answers (2)

Sebb
Sebb

Reputation: 911

This does not only heavily affect performance, but also the RAM usage will skyrocket. From the PHP manual:

If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object.

This shows that for every prepare, the database will be queried and an RAM-using object is created. This shouldn't be a big deal for 2-4 statements, but with 10+ statements this will

  • put load on your SQL
  • therefore put load on your network (if your SQL is located outside)
  • create a lot of useless objects
  • require a lot of time for this and
  • set a lot of variables in your current scope you don't need

You could numerate/name your querys and have a factory which will create it for you, so you could just do

factory::getQuery(4)->execute();

I have written a code gen for this which makes things a lot easier; it's not ready for the public but there are others.

Upvotes: 1

meda
meda

Reputation: 45490

You should prepare once , then execute multiple times:

$sqlMyQuery = $db->prepare('SELECT * FROM tblTable1 WHERE Column1 = :Column1');

$sqlMyQuery1Param = array(':Column1' => $_POST[FormItem1]);
$sqlMyQuery2Param = array(':Column1' => $_POST[FormItem2]);
$sqlMyQuery3Param = array(':Column1' => $_POST[FormItem3]);

$result1 = $sqlMyQuery-> execute($sqlMyQuery1Param);

This will improve performance for sure. Calling prepare() on the same query defeats the purpose of prepare statment and could be expensive sometimes.

Upvotes: 0

Related Questions