[Dbix-Class] Sybase paging performance regressions

[thomas Dauby]

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(): Generic
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, but
the generated SQL is painfully slow:

SELECT COUNT( * ) FROM job_exec me JOIN host host ON host.id =3D me.host_id
JOIN users users ON users.id =3D me.users_id JOIN job job ON job.id =3D
me.job_id JOIN application application ON application.id =3D
job.application_id JOIN environment environment ON environment.id =3D
application.environment_id WHERE ( return_status NOT IN ( ? ) ): '0'

SELECT environment_name, start_time, end_time, id, return_status,
application_name, job_name, duration FROM ( SELECT environment.name AS
environment_name, me.start_time, me.end_time, me.id, me.return_status,
application.name AS application_name, job.name AS job_name, me.duration FROM
job_exec me JOIN host host ON host.id =3D me.host_id JOIN users users ON
users.id =3D me.users_id JOIN job job ON job.id =3D me.job_id JOIN applicat=
ion
application ON application.id =3D job.application_id JOIN environment
environment ON environment.id =3D application.environment_id WHERE (
return_status NOT IN ( ? ) ) ) me WHERE ( SELECT COUNT(*) FROM job_exec
rownum__emulation WHERE rownum__emulation.id > me.id ) BETWEEN 690 AND 719
ORDER BY me.id DESC : '0'

FYI, here's the generated SQL using the previous version:

SELECT COUNT( * ) FROM job_exec me JOIN host host ON host.id =3D me.host_id
JOIN users users ON users.id =3D me.users_id JOIN job job ON job.id =3D
me.job_id JOIN application application ON application.id =3D
job.application_id JOIN environment environment ON environment.id =3D
application.environment_id WHERE ( ( environment.name =3D ? AND return_stat=
us
NOT IN ( ? ) ) ): 'paris_divers', '0'

SELECT environment.name, me.start_time, me.end_time, me.id,
me.return_status, application.name, job.name, me.duration FROM job_exec me
JOIN host host ON host.id =3D me.host_id JOIN users users ON users.id =3D
me.users_id JOIN job job ON job.id =3D me.job_id JOIN application applicati=
on
ON application.id =3D job.application_id JOIN environment environment ON
environment.id =3D application.environment_id WHERE ( ( 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?

Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100621/9ba=
7f389/attachment.htm