in reply to DBI question with placeholders and IN

my $values = join ",", @values; # This is 1 to N values

Unless you have a fixed (or a small) 'N', it may not be worth using prepare_cached. You would have to have a '?' for every value, and use an array of values in the 'execute' like this:

...where some column in (" . join(',',('?') x @values) . ")"
...
$sth->execute(@values) # one value for every '?' above

[download]
For a large 'N' (or if this code is only executed once) don't use prepare_cached, and just 'prepare' every time.

Replies are listed 'Best First'.
Re: Re: DBI question with placeholders and IN
by dragonchild (Archbishop) on Oct 01, 2002 at 14:01 UTC
    Ok. Cool. I know about the array of ? now.

    However, 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 and this query forms the basis for a report that's generated many, many times with different values.) If I'm working with near-limitless cache, why shouldn't I prepare_cached everything?

    ------
    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

[reply]
      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...

[reply]
        What if I call prepare_cached() and finish() on all my statements. Don't I get a performance benefit from using prepare_cached?

        ------
        We are the carpenters and bricklayers of the Information Age.

        Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

[reply]

In Section Seekers of Perl Wisdom