in reply to Re: Re: DBI question with placeholders and IN
in thread DBI question with placeholders and IN

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...

  • Comment on Re: Re: Re: DBI question with placeholders and IN

Replies are listed 'Best First'.
Re(4): DBI question with placeholders and IN
by dragonchild (Archbishop) on Oct 02, 2002 at 16:36 UTC
    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]
      Don't I get a performance benefit from using prepare_cached?

      It depends. Not on any statements that are executed only once. Have you read the DBI docs on how prepare_cached actually works? It stores the actual SQL statement (and any attributes) as a hash key whose value is the statement handle. If your program happens to prepare the same SQL statement twice within the same invocation of your program, you will get back the same statement handle. So its actually useless for statements that are only executed once. And I usually prefer to organize my logic so that statements only get prepared once anyway, so I normally have little use for prepare_cached (even though I'm the one that came up with the insert_hash example in the DBI docs). But even in this case I still don't think the performance benefit justifies caching up to 1000 handles for this one bit of code even if its running under mod_perl (meaning every child process would be caching up to 1000 statement handles).

      And you still haven't answered whether this is under CGI or under something like mod_perl. Nor how many times will that statement be prepared in one run of the program?

[reply]

In Section Seekers of Perl Wisdom