>However, since in my application the number of values in the array can vary, I'm wondering if using ANY has the benefit of causing the driver/DB to cache a smaller number of prepared statement.
Right you are. In case you use Array, the whole thing is passed as a single bind, and it results in a single prepared statement.
Naive use of (?, ?, ?, ?, ?) would result in multiple prepared statements that would consume memory at both client and server sides.
Right. I didn't think you'd do that, I assumed you'd interpolate properly quoted SQL literals. Not only is that more vulnerable to potential issues if your quoting is flawed, it often uses more memory and CPU for the string processing, so an array bind is definitely better.
I had my "writing TAP tests in Perl" hat on, not my "developing sane SQL" hat on. Sorry.
1) Note: sometimes you might want to use (?, ?, ?) kind of syntax (e.g. to support DBs that cannot process arrays), then you might want to use "power of two" placeholders, and fill the excessive ones with null value or with a repetition of the last value.
For instance, if you want to pass 5 values 1,2,3,4,5 you'd better pass it as
(1,2,3,4,5,5,5,5) (of course, use ?,?,?,?, ?,?,?,? )
I do not advice you to use that extensively, however it is a nice to know trick.