Skip Menu |
 

This queue is for tickets about the DBIx-SearchBuilder CPAN distribution.

Report information
The Basics
Id: 41912
Status: resolved
Priority: 0/
Queue: DBIx-SearchBuilder

People
Owner: RUZ [...] cpan.org
Requestors: ktm [...] rice.edu
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.54
Fixed in: 1.55



Subject: Handle::Pg uses a poorly performing definition of DistinctQuery
Download (untitled) / with headers
text/plain 1.6k
The definition for DistinctQuery for PostgreSQL is very slow. Replacing it with the definition used in Handle::Oracle, below, results in a significant performance increase in RT with a PostgreSQL backend. Thank you for your consideration, Ken sub DistinctQuery { my $self = shift; my $statementref = shift; my $sb = shift; # when we have group by clause then the result set is distinct as # it must contain only columns we group by or results of aggregate # functions which give one result per group, so we can skip # DISTINCTing if ( my $group = $sb->_GroupClause ) { $$statementref = "SELECT main.* FROM $$statementref"; $$statementref .= $group; $$statementref .= $sb->_OrderClause; return; } my $table = $sb->Table; if ($sb->_OrderClause =~ /(?<!main)\./) { # If we are ordering by something not in 'main', we need to GROUP # BY and adjust the ORDER_BY accordingly local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD => 'id'}]; local $sb->{order_by} = [map {($_->{ALIAS} and $_->{ALIAS} ne "main") ? {%{$_}, FIELD => "min(".$_->{FIELD}.")"}: $_} @{$sb->{order_by}}]; my $group = $sb->_GroupClause; my $order = $sb->_OrderClause; $$statementref = "SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)"; } else { $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) "; $$statementref .= $sb->_GroupClause; $$statementref .= $sb->_OrderClause; } }
Download (untitled) / with headers
text/plain 2.1k
Hi, In 1.55 we changed way we generate query for Pg because of instability of the solution. New way is different and Oracles variant is also different because of some Oracle specific changes. I believe this bug can be closed. Feel free to open it again if you think that I missed main point of your complain. On Tue Dec 23 12:52:22 2008, ktm@rice.edu wrote: Show quoted text
> The definition for DistinctQuery for PostgreSQL is very slow. Replacing > it with the definition used in Handle::Oracle, below, results in a > significant performance increase in RT with a PostgreSQL backend. > > Thank you for your consideration, > Ken > > sub DistinctQuery { > my $self = shift; > my $statementref = shift; > my $sb = shift; > > # when we have group by clause then the result set is distinct as > # it must contain only columns we group by or results of aggregate > # functions which give one result per group, so we can skip > # DISTINCTing > if ( my $group = $sb->_GroupClause ) { > $$statementref = "SELECT main.* FROM $$statementref"; > $$statementref .= $group; > $$statementref .= $sb->_OrderClause; > return; > } > > my $table = $sb->Table; > > if ($sb->_OrderClause =~ /(?<!main)\./) { > # If we are ordering by something not in 'main', we need to
GROUP Show quoted text
> # BY and adjust the ORDER_BY accordingly > local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD =>
'id'}]; Show quoted text
> local $sb->{order_by} = [map {($_->{ALIAS} and $_->{ALIAS} ne > "main") ? {%{$_}, FIELD => "min(".$_->{FIELD}.")"}: $_} @{$sb- >{order_by}}]; > my $group = $sb->_GroupClause; > my $order = $sb->_OrderClause; > $$statementref = "SELECT main.* FROM ( SELECT main.id FROM > $$statementref $group $order ) distinctquery, $table main WHERE
(main.id Show quoted text
> = distinctquery.id)"; > } else { > $$statementref = "SELECT main.* FROM ( SELECT DISTINCT
main.id Show quoted text
> FROM $$statementref ) distinctquery, $table main WHERE (main.id = > distinctquery.id) "; > $$statementref .= $sb->_GroupClause; > $$statementref .= $sb->_OrderClause; > } > }
-- Best regards, Ruslan.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.