I would avoid your prepare_cached-execute loop.

The performance of a prepare with repeated executes compared to a repeated prepare-execute is highly driver, database engine and platform dependent. You should carefully test and bendhmark before choosing.

The optimizer that fails over to a tabel scan for an IN clause should be regarded as underpowered. A usefull optimizer should be able to fiund out when it is beneficial to do a tabel sacn and when a repeated series of index/key lookups based on the query. If you areunfortunate enough to be hampered by an optimzer of this kind, then you have to carefully craft your queries to aid the optimizer (and yes, it might be useful to just fetch one value pr execute).

This is one of the main reasons that is it impossible to state anything about performance of queries.

One of the things that irks me most when reading about DBI in the monestary is that people assume that preparing a statement actually speeds things up. This is not always true. The Ingres optimizer does its work based on the values in the query, and can therefore not do its work before the values actually are given (in the execute call), so a prepare does not help very much here!

Otoh I am given to understand that Oracle and others do some optimization without knowing the datavalues.

In short: YMMV. But if you discover that the prepare execute loop is faster than the select with IN, then you should probably consider changing database vendor ;-) It should be faster to issue one large query rather than a series of small queries - give or take a bit.

BTW: Don't bug gbarr about the DBI documentation. The author is Tim Bunce

In reply to Re: Re (3): DBI question with placeholders and IN by htoug
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.