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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
