[Dbix-Class] Sybase paging performance regressions

[Peter Rabbitson]

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(): 
> 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 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> = me.host_id JOIN users users ON 
> users.id <http://users.id> = me.users_id JOIN job job ON job.id 
> <http://job.id> = me.job_id JOIN application application ON 
> application.id <http://application.id> = job.application_id JOIN 
> environment environment ON environment.id <http://environment.id> = 
> 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> = me.host_id JOIN 
> users users ON users.id <http://users.id> = me.users_id JOIN job job ON 
> job.id <http://job.id> = me.job_id JOIN application application ON 
> application.id <http://application.id> = job.application_id JOIN 
> environment environment ON environment.id <http://environment.id> = 
> application.environment_id WHERE ( ( environment.name 
> <http://environment.name> = ? 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 => 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 more
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=dbsrgits/DBIx-Class.git;a=blob;f=t/75limit.t;h=686161af223473ebd475d6c563cf71a395dce8ab;hb=refs/heads/master#l29