Re(4): DBI question with placeholders and IN

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.

Comment on Re(4): DBI question with placeholders and IN

Replies are listed 'Best First'.
Re: Re(4): DBI question with placeholders and IN by runrig (Abbot) on Oct 02, 2002 at 19:22 UTC
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]

Replies are listed 'Best First'.

Re: Re(4): DBI question with placeholders and IN
by runrig (Abbot) on Oct 02, 2002 at 19:22 UTC

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]