[Dbix-class] Subquery overhead with slice()

Oleg Pronin syber.rus at gmail.com
Wed Aug 6 12:51:40 BST 2008


And without subquery at all if you make use of 'having r >=3D 9'

2008/8/6 Nickolay Platonov <nickolay8 at gmail.com>

> 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 =3D me.current_photo_id )
>     ) A
>     WHERE ROWNUM < 16
> ) B
> WHERE r >=3D 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 =3D me.current_photo_id )
>                 WHERE
>           ROWNUM < 16
> ) B
> WHERE r >=3D 9
>
>
> _______________________________________________
> 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/dbix-class@lists.rawmode.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080806/915=
4164e/attachment.htm


More information about the DBIx-Class mailing list