[Dbix-class] Subquery overhead with slice()

[Nickolay Platonov]

You mean having r >=3D 9 and r<16?

On Wed, Aug 6, 2008 at 3:51 PM, Oleg Pronin <syber.rus at gmail.com> wrote:

> 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 O=
N (
>> 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
>>
>
>
> _______________________________________________
> 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/98b=
54e7d/attachment.htm