Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
Yes, but it can drastically degrade performance (in very rare circumstances which I'll describe below).

Don't get me wrong, the default position should always be to use binds. Not only for the reasons that have been mentioned by others, but also because it can improve database performance in other, less direct ways. For example (speaking from experience with Oracle, in particular here, but I imagine that other RDBMSs have similar issues): even if you do not cache your statement handles, or use prepare_cached (and there are very good reasons not to do so, sadly), the database server, itself, caches various information based on the sql text. When it reencounters the same (textually) sql that it has seen before, it just looks up things like the parse-tree and the execution path, instead of recomputing them. Thus, if you bind your sql, and even if you throw away the statement handle... the database server doesn't, so not all of the prepare step is repeated. (Of course, some of the work is repeated, so it's still better to reuse your statement handles if you can.)

Anyway, I promised an example of drastically degraded performance with bound variables, and here it is: cost-based optimization. Say you've got a table ("thing") with a column called "type", and a million rows. Of those million rows, 50 are "type='special'" and all of the rest are "type='general'. You also have an index on thing.type. Now, run the query:

select * from thing where type=?
What is the proper execution plan? Should you use the index or not? Who knows? It depends on the value of the bind-parameter. If its value is 'general' then, hell no, you should not use the index. If its value is 'special', then hell yes, you should use the index. In fact, if it is anything other than 'general' you should. A CBO with histogram statistics would know the proper plan, if the value weren't bound.

Anyway, that's an overly simplistic example, but the thing is: this can and does happen. Just this week, I was optimizing a query that executed in about a minute with all of its values unbound, but took essentially an infinite amount of time when the values were bound. Same exact query.

Truth is, yes, start from the position of binding all of your literal values, but serious DB tuners can, and do, find situations where bind values can bite you in the ass.


------------
:Wq
Not an editor command: Wq

In reply to Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by etcshadow
in thread Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE by jZed

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (5)
As of 2023-02-05 23:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer not to run the latest version of Perl because:







    Results (33 votes). Check out past polls.

    Notices?