0

I'm starting a new project shortly and going to use coding standards. I've always written SQL statements like this:

$sql = sprintf("INSERT INTO users (name) VALUES ('%s')", $name);

I'm wondering if there is any performance gained by using one of these:

$sql = "INSERT INTO users (name) VALUES ('".$name."')";
$sql = "INSERT INTO users (name) VALUES ('$name')";

Also: Does this performance difference fluctuate with the addition of more "parameters" (as in the case of the first line of code) ?

Thanks.

Tom
  • 2,973
  • 3
  • 28
  • 32
  • 1
    What's your excuse for not using prepared statements? –  Jan 07 '12 at 21:30
  • I'm new to prepared statements and thinking about using them yes, would still be interesting to know though – Tom Jan 07 '12 at 21:32
  • Have you tried benchmarking these? – Wiseguy Jan 07 '12 at 21:32
  • Syntax is not going to affect performance, data manipulation will. If you want concrete answers to this, benchmark the code in your environment. Voting to close (too localized). And I agree with Tim, use prepared statements instead of mashing together SQL strings. – Wesley Murch Jan 07 '12 at 21:33
  • 2
    This is not a good idea. Imagine what will happen if you have `$name = "'); drop table ***"; ...`. Go for prepared statements if you need measurable performance gain. – Doncho Gunchev Jan 07 '12 at 21:38

2 Answers2

4

Yes, this will increase performance. sprintf is an additonal function call, your string must be scanned for the %s which requires additional time.

The second option using the string concat operator (.) is faster, but the third alternative, just placing the string variable in a string is fastest due to other optimizations that PHP performs.

Anyway, while investigating how PHP deals with string concatination and how it performs is interesting you should never create sql queries like this, because it opens your code to SQL injections. apply mysql_real_escape() to your parameters first or use prepared statements.

yankee
  • 38,872
  • 15
  • 103
  • 162
1

My tests don't show $a $b is any faster than $a . ' ' . $b – and sometimes it's even slower.

But both are marginal. Usually you want sprintf when you have three or more variables.

Emil
  • 7,220
  • 17
  • 76
  • 135
Ralf Lang
  • 11
  • 1