[Dbix-class] Turning off prepared statements for LIKE searches

[Toby Corkindale]

Toby Corkindale wrote:
> Jacob Bunk Nielsen wrote:
>> Hi
>>
>> I have read an interesting blog post about how prepared statements in
>> PostgreSQL does not work well with LIKE searches. It can be found at:
>> http://blog.endpoint.com/2009/08/debugging-prepared-statements.html
>>
>> I'm hit by this in several places in my code. I have an example where I
>> can go from ~290 ms to 0.5 ms for a single query, so quite a significant
>> improvement, that I'd like to benefit from.
>>
>> Of course I'm using DBIx::Class, so my question is how do I turn off
>> prepared statements for LIKE searches?
>>
>> I think I'd like to be able to turn off prepared statements everywhere I
>> use LIKE. If that is not what I want, please tell me why :-)
>>
>> Can anyone give my a clue as to how I would go about not using prepared
>> statements for LIKE searches and still staying with DBIx::Class?
> 
> I believe you should just be able to pass the relevant connect options, ie:
> 
> my $dbh = DBI->connect(
>     'dbi:Pg:dbname=myDatabase', $user, $passwd,
>     { pg_server_prepare => 0 }
> );

... however, that said, DBD::Pg doesn't force you to disable prepared 
statements across the whole connection, as generally that'll have a 
negative performance impact.

You can disable prepared statements on a per-query basis if you like, 
but I just don't know how to go about causing DBIx::Class to pass the 
relevant flags over.

If it's one one or two complex queries that you want to optimise, then 
you could do it with a custom ResultSource, or a ResultSet method, which 
acts on the raw $dbh to build a query instead.
Have a look at the DBIx::Class::Manual::Cookbook for more info on those.

Cheers,
Toby