Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^3: Dynamic SQL

by bitingduck (Chaplain)
on Apr 06, 2015 at 15:57 UTC ( #1122574=note: print w/replies, xml ) Need Help??


in reply to Re^2: Dynamic SQL
in thread Dynamic SQL

Did you notice that my version has two separate statement handles that are being executed? You can do the same thing with yours. Prepared statements with placeholders will likely be faster than constructing queries on the fly and executing them, and certainly much safer.

How many possible queries can you need to construct that you can't afford to use prepared statements and placeholders?

Replies are listed 'Best First'.
Re^4: Dynamic SQL
by erix (Parson) on Apr 06, 2015 at 16:02 UTC
    prepared statements with placeholders will likely be faster than constructing queries on the fly

    I don't think rather doubt this is generally true. It would be nice to see it demonstrated.

      prepared statements with placeholders will likely be faster than constructing queries on the fly
      I rather doubt this is generally true. It would be nice to see it demonstrated.

      Running a ton of nearly identical queries can get a rather slight performance improvement from only having to prepare the statement once. But this is not particularly significant in the most extreme case and is of no significance at all if your queries are not rather trivial.

      On the other hand, in all 3 of my most recent jobs I've had to basically disable the "prepare" step as the poor query optimization that is done in the absence of the actual values is often horrible and can result in queries taking several orders of magnitude longer.

      So I've heard that newer versions of databases that I haven't used recently end up doing query planning twice, once at "prepare" time and then again once the values are known. I don't know how much that impacts the oft-touted "efficiency" of separate prepare/execute, but it surely means it has become even more insignificant.

      But the documentation says that it is faster so everybody repeats that, usually with much more emphasis than is warranted (and completely ignoring the much, much worse performance problems that can result).

      - tye        

      If each prepared statement is used only once then, no, it won't be true. If each prepared statement is used a number of times then it will save the parsing time of the SQL queries each time a prepared statement is reused. The OP has given very limited examples and no sense of the number of possible unique (modulo the values that would go in the placeholders) queries

        If each prepared statement is used only once then, no, it won't be true.

        for instance.

        And then there is the fickleness of planning.

        But I can see it would be hard to set up a broad, convincing case.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1122574]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2018-07-16 05:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?















    Results (332 votes). Check out past polls.

    Notices?