SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member drlinux's Avatar
    Join Date
    Aug 2010
    Location
    Norway
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQLi prepared statements for speed?

    Leaving aside the benefit for security, does mysqli prepared statements have any performance benefit?

    As I understand it, the prepare stores the query in parsed form at the database server, and when doing execute, then only the data for any values are passed from script to database.

    Direct benefit would be that the database only need to parse SQL once, and that less bytes (only the values) are sent on each query.

    However, is this not only a benefit if the query is executed repeatedly inside the script, or does MySQL store the prepared statement between requests? If so, for how long?

  2. #2
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Statements are linked to a connection, so as soon as the connection is terminated, the stored statements are freed (at least, that's my understanding. Someone can correct me if I'm wrong). As a result, prepared statements in any language tend to suffer a very slight performance decrease with single statements and a modest performance increase with very many repeated queries per connection.

    Security is the real reason, though. As I like to put it: the difference in total time spent between a standard single query and a prepared single query will never be sufficient, over all the queries run on your server, to come within two orders of magnitude of the time it will take you to clean up one SQL Injection attack.

  3. #3
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Prepared statements are slower unless you're using them as they're intended and you're running the same query multiple times with different parameters.

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They only live in the connection. In fact, they only live as long as you reuse the same resource. Eg. If you prepare the same query twice, you will not reuse the first one. You need to hold on to that prepared statement.

    There is some performance gain, if you're calling the same query multiple times. I'm not sure how many, but if you're calling hundreds or thousands of queries, it makes a difference. If you're looking to boost performance on write operations, a good trick is to use a transaction. Since indexes are only updated once the transaction is committed, rather than between each query, you can often gain a lot by this simple trick.

  5. #5
    SitePoint Member drlinux's Avatar
    Join Date
    Aug 2010
    Location
    Norway
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, that confirms my suspicion.

    Regarding lifetime of how long MySQL store the prepared statement, will it survive across requests if using persistent connection, and is there then a way to check if already prepared?

    I'm thinking of simple queries that are executed each time a script is called. I don't think it may apply in this case, but I'll keep the transaction trick in mind

  6. #6
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    It won't persist, because it creates a stmt object related to the prepared statement. There's no way (at least afaik) to retrieve this on subsequent requests.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,146
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MySQL
    Sometimes prepared statements can actually be slower than regular queries. The reason for this is that there are two round-trips to the server, which can slow down simple queries that are only executed a single time. In cases like that, one has to decide if it is worth trading off the performance impact of this extra round-trip in order to gain the security benefits of using prepared statements.
    source: http://dev.mysql.com/tech-resources/...tatements.html

    Whether or not its a noticeable difference will be dependent on the environment.
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TomB View Post
    Prepared statements are slower unless you're using them as they're intended and you're running the same query multiple times with different parameters.
    You're right, of course, when it comes to speed. From what I've seen, not having your indexes right, not using efficient queries, using many queries to do something you could do with one: those are things that will really slow you down.

    Every time I see this question, I have the sudden urge to shout: yes, they might impose a (very marginal) performance decrease, but you should use them none the less: security is more important than speed in my book, and this way, security is implied without you having to do anything about it.
    Yes, I blog, too.

  9. #9
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Well the OP specifically wanted to discuss performance ignoring security benefits and prepared statements aren't the only way to secure your queries.

    As for "using many queries to do something you could do with one" that's not always slower. This was something I firmly believed too until Czaries questioned my assumption here: http://www.sitepoint.com/forums/show...2&postcount=10

    If you benchmark it, he's right.

  10. #10
    SitePoint Addict webaddictz's Avatar
    Join Date
    Feb 2006
    Location
    Netherlands
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TomB View Post
    Well the OP specifically wanted to discuss performance ignoring security benefits and prepared statements aren't the only way to secure your queries.
    I know he wants to wants discuss the performance, but from where I'm standing, performance also includes the hoops you'll have to jump when you have to escape manually. I just thought it should be said at least once

    Quote Originally Posted by TomB View Post
    As for "using many queries to do something you could do with one" that's not always slower. This was something I firmly believed too until Czaries questioned my assumption here: http://www.sitepoint.com/forums/show...2&postcount=10
    I know that, I wasn't trying to say one query is *always* more efficient, but there certainly are cases where that is true. You'll have to try and see for each case individually though.
    Yes, I blog, too.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •