[Dbix-Class] Sybase paging performance regressions

[thomas Dauby]

2010/6/21 Peter Rabbitson <rabbit+dbic at rabbit.us <rabbit%2Bdbic at rabbit.us>>

> thomas Dauby wrote:
>
>> Hi all,
>>
>> I recently upgraded to DBIx::Class 0.08123, and paging does not work
>> anymore.
>>
>> I get the following error:
>>
>> query: Error while executing query:DBIx::Class::ResultSet::next(): Gener=
ic
>> Subquery Limit order criteria column 'me.start_time' must be unique (no
>> unique constraint found) at VTOM/Repo2.pm line 322 at WEB/Action.pm line=
 119
>>
>> I modified to order by clause to use a column with a unique contraint, b=
ut
>> the generated SQL is painfully slow:
>>
>>   > SELECT environment_name, start_time, end_time, id, return_status,
>
>> application_name, job_name, duration FROM ( SELECT environment.name <
>> http://environment.name> AS environment_name, me.start_time, me.end_time,
>> me.id <http://me.id>, me.return_status, application.name <
>> http://application.name> AS application_name, job.name <http://job.name>
>> AS job_name, me.duration FROM job_exec me JOIN host host ON host.id <
>> http://host.id> =3D me.host_id JOIN users users ON users.id <
>> http://users.id> =3D me.users_id JOIN job job ON job.id <http://job.id> =
=3D
>> me.job_id JOIN application application ON application.id <
>> http://application.id> =3D job.application_id JOIN environment environme=
nt
>> ON environment.id <http://environment.id> =3D application.environment_id
>> WHERE ( return_status NOT IN ( ? ) ) ) me WHERE ( SELECT COUNT(*) FROM
>> job_exec rownum__emulation WHERE rownum__emulation.id <
>> http://rownum__emulation.id> > me.id <http://me.id> ) BETWEEN 690 AND 719
>> ORDER BY me.id <http://me.id> DESC : '0'
>>
>>
>> FYI, here's the generated SQL using the previous version:
>>
>>   > SELECT environment.name <http://environment.name>, me.start_time,
>
>> me.end_time, me.id <http://me.id>, me.return_status, application.name <
>> http://application.name>, job.name <http://job.name>, me.duration FROM
>> job_exec me JOIN host host ON host.id <http://host.id> =3D me.host_id JO=
IN
>> users users ON users.id <http://users.id> =3D me.users_id JOIN job job ON
>> job.id <http://job.id> =3D me.job_id JOIN application application ON
>> application.id <http://application.id> =3D job.application_id JOIN
>> environment environment ON environment.id <http://environment.id> =3D
>> application.environment_id WHERE ( ( environment.name <
>> http://environment.name> =3D ? AND return_status NOT IN ( ? ) ) ) ORDER =
BY
>> start_time desc: 'paris_divers', '0'
>>
>>
>> The new SQL comes straight from DBIx::Class::SQLAHacks, but I dont know
>> where the old sql comes from...
>>
>> Is there a way to go back to the old method? I'm not familiar enough with
>> the codebase and the code flow to modify it, but I'm using Sybase 15, so=
 for
>> this operations, I guess scrollable cursors could be used. What shall I
>> modify to do so?
>>
>>
> Before 08122 DBIx::Class would default to software_limit =3D> 1 when used
> with Sybase, because the generic subquery method didn't function at all.
> While software_limit is not documented (stupid omission, will be fixed in
> the next version) it essentially removes *all* limits from the SQL query
> and does the "fast-forwarding" on the client side within DBIC. For a usage
> example see [1]. So this is how you can switch between the new and old
> method, depending on what will be more beneficial for you.
>
> On your suggestion of "scrollable cursors" - we are very interested into
> having this kind of functionality baked into DBIC, could you elaborate mo=
re
> on how this could work? (best show a pure-DBI example, it will be clearer
> for us how to incorporate that into the DBIC machinery)
>
> Cheers!
>
> [1]
> http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=3Ddbsrgits/DBIx-Class.git;=
a=3Dblob;f=3Dt/75limit.t;h=3D686161af223473ebd475d6c563cf71a395dce8ab;hb=3D=
refs/heads/master#l29
>
> _______________________________________________
> 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.scsys.co.uk
>


Thanks, it worked!
I see no easy way to use cursors in DBD::Sybase.

Here's how I'd do it:

use strict ;
> use warnings;
> use DBD::Sybase;
> use Data::Dumper;
>
> my $conn =3D
> DBI->connect('dbi:Sybase:'.$ENV{DSQUERY},$ENV{DSUSER},$ENV{DSPASSWD},
>                         {'loginTimeout' =3D> 5,'timeout' =3D> 10,'PrintEr=
ror'
> =3D> 1,'AutoCommit' =3D> 1,syb_chained_txn =3D> 0}) || die "Error while
> connecting";
>
> # declare cursor must be the only statement in a query batch
>
$conn->do(q{
> declare process_Csr *scroll* cursor for
> select name, type
> from sysobjects
> order by name
> });
>
> my $sth =3D $conn->prepare(q{
> /*
> here we could use
> set cursor rows 10
> to fetch 10 rows and stop, instead of using @cnt
> */
>
> open process_Csr
>
> declare @cnt int
> select @cnt =3D 1
>
> -- go to row 4 and fetch
> fetch absolute 4 process_Csr
>
> while ( @@sqlstatus =3D 0 AND @cnt < 10 )
> begin
>     select @cnt =3D @cnt + 1
>     fetch process_Csr
> end
>
> }) || die "Error while preparing statement\n";
>
> $sth->execute();
>
> {
>     my $row;
>
>     while($row =3D $sth->fetch) {
>         print Dumper\ $row;
>     }
>     # returns one resultset per cursor fetch
>     redo if $sth->{syb_more_results};
>
> }
>

Thanks a lot,
Thomas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100621/1a1=
eea48/attachment-0001.htm