[Dbix-class] Subquery overhead with slice()

Matt S Trout dbix-class at trout.me.uk
Fri Aug 8 02:50:09 BST 2008


On Wed, Aug 06, 2008 at 03:35:08PM +0400, Nickolay Platonov wrote:
> Hi all
> 
> Not sure I'm posting to appropriate maillist, may be someone will advise
> where to forward this.
> 
> I'd like to draw attention on 1 useless subquery, which is produces by
> slice() for DBIx::Class::Storage::DBI::Oracle::Generic
> 
> for example $rs->slice(8,15) will produce following query, containing 2
> subqueries:
> 
> SELECT * FROM
> (
>     SELECT A.*, ROWNUM r FROM
>     (
>         SELECT me.id AS col1, me.name AS col2, me.description AS col3,
> current_photo.path AS col4 FROM place me LEFT JOIN photo current_photo ON (
> current_photo.id = me.current_photo_id )
>     ) A
>     WHERE ROWNUM < 16
> ) B
> WHERE r >= 9
> 
> 
> meanwhile exactly the same result can be achieved with only 1 subquery:
> 
> SELECT * FROM
> (
>         SELECT
>           me.id AS col1, me.name AS col2, me.description AS col3,
> current_photo.path AS col4, ROWNUM AS r
>         FROM
>           place me
>         LEFT JOIN photo current_photo
>           ON ( current_photo.id = me.current_photo_id )
>                 WHERE
>           ROWNUM < 16
> ) B
> WHERE r >= 9

I don't think that would work if the innermost query has a GROUP BY
and HAVING clause though.

I'd love to see a patch that detects if it can optimise this and does
so though.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/



More information about the DBIx-Class mailing list