Skip Menu |
 

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

Report information
The Basics
Id: 61277
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: ejtrochim [...] alaska.edu
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.08123
Fixed in: 0.08124



Subject: DBIC creates sub-optimal Oracle ROWNUM queries
Download (untitled) / with headers
text/plain 1.4k
I've discovered that the query that DBIC generates when doing Oracle ROWNUM limiting is sub-optimal. I found it when I ran the following DBIC search against an Oracle database (this is from a Catalyst application I just started writing). my $granules = $c->model('DB::Granule')->search(undef, {'offset' => $offset, 'rows' => $length, 'columns' => [qw(granule_name data_granule_type platform_type)]}); This generates the following SQL: SELECT granule_name, data_granule_type, platform_type FROM ( SELECT granule_name, data_granule_type, platform_type, ROWNUM rownum__index FROM ( SELECT me.granule_name, me.data_granule_type, me.platform_type FROM granule me ) me ) me WHERE rownum__index BETWEEN 11 AND 20 The granules table contains almost 3.5 million rows. On my Oracle 10g database the query takes 3.8 seconds to execute. I modified SQLAHacks.pm to instead produce the following query for the same call to DBIC search: SELECT granule_name, data_granule_type, platform_type FROM ( SELECT granule_name, data_granule_type, platform_type, ROWNUM rownum__index FROM ( SELECT me.granule_name, me.data_granule_type, me.platform_type FROM granule me ) me WHERE ROWNUM <= 20 ) me WHERE rownum__index >= 11 This query takes .06 seconds to execute. Attached is a patch of my changes. Note that I am new to DBIC (I just started using about two days ago) so I am not that familiar with the code. I don't believe this will negatively affect other database engines although I don't know for sure.
Subject: SQLAHacks.diff
Download SQLAHacks.diff
text/x-diff 604b
--- SQLAHacks.pm 2010-09-11 11:15:33.000000000 -0800 +++ SQLAHacks.pm.orig 2010-09-11 11:15:26.000000000 -0800 @@ -281,14 +281,13 @@ my $idx_name = $self->_quote ('rownum__index'); my $order_group_having = $self->_parse_rs_attrs($rs_attrs); - $sql = sprintf (<<EOS, $offset + $rows, $offset + 1, ); + $sql = sprintf (<<EOS, $offset + 1, $offset + $rows, ); SELECT $outsel FROM ( SELECT $outsel, ROWNUM $idx_name FROM ( SELECT $insel ${sql}${order_group_having} ) $qalias - WHERE ROWNUM <= %u -) $qalias WHERE $idx_name >= %u +) $qalias WHERE $idx_name BETWEEN %u AND %u EOS
Download (untitled) / with headers
text/plain 419b
On Sat Sep 11 19:05:32 2010, ejtrochim wrote: Show quoted text
> I've discovered that the query that DBIC generates when doing Oracle > ROWNUM limiting is sub-optimal.
Nice! Patched based on your suggestion, but with even leaner SQL (differing depending on whether offset is present or not): http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=f60e2dcb98f9041f0fbfa60cf71037d3814ae36d#patch2 Thanks!


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.