I am curious why you would just prepare and not prepare_cached. I do have a large N (in some cases, up to 999), but I will probably re-issue this query a whole bunch of times. (It's in a web application...

Is it a CGI app, or some persistent environment (mod_perl, etc.)? If its CGI and this query is only executed once per hit, then there is no benefit to prepare_cached. And even if you do execute this 1000 times per hit (or if this is mod_perl), with N being anywhere from 1 to 999, you will see little to no (and probably negative) benefit with (IMHO) too much cost.

Every cursor you prepare and don't release consumes resources (memory, etc.), and you may run out. (With Oracle and some other DB's) there is a limit on the number of active statement handles you can have per session and I'm not sure if 999 is anywhere near that, but I think its a bad idea to use prepare_cached on a statement that dynamic. See this node for someone who ran out of memory (its not quite the same since he wasn't even using placeholders, but its the most relevant node I can find).

Update: Also, its not so much the size of N, but the number of different 'N's you'll be preparing. If you know, e.g., that N is always 996 <= N <= 999, and you'll be executing this statement 1000 times on every invocation of the script, then prepare_cached might be a good thing in that case since it only has to cache up to 4 different statements...

In reply to Re: Re: Re: DBI question with placeholders and IN by runrig
in thread DBI question with placeholders and IN by dragonchild

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


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.