| Subject: | DBIC creates sub-optimal Oracle ROWNUM queries |
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 |
--- 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