If you are not executing the same SQL with the same attributes passed to prepare each time then each call to prepare_cached will give you a new statement handle and your SQL will be parsed each time. You only get an advantage from prepare_cached when the SQL and attributes to prepare are the same as a previous call to prepare_cached.

You said If the function is called in a loop, then it will be in-efficient and all I was attempting to say was if you are working with SQL in a loop you can prepare the SQL once and execute it multiple times in the loop but that does not necessarily suggest using prepare_cached instead of prepare. Where prepare_cached might speed things up (although with risks) would be if you called the sub containing the loop repeatedly. The second and subsequent times the sub was called the SQL would not be parsed.

I hope see the distinction I was trying to make.

prepare_cached is a useful tool but also dangerous unless used properly. I've see code where someone used prepare_cached everywhere just because they thought it was quicker but it a) created hundreds of statement handles and b) used loads of resource in the database and c) did not work reliably because the same statement was used in multiple places when one of the previous instances was still active.

In reply to Re^4: dbi: prepare vs prepare_cached -- which is best ? by mje
in thread dbi: prepare vs prepare_cached -- which is best ? by sathiya.sw

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.