We have a view that, without constraints, will return 90 million rows and a reporting application that needs to display paged datasets of that view.
We're using nhibernate and recently noticed that its paging mechanism looks like this:
select * from (select rownumber() over() as rownum,
this_.COL1 as COL1_20_0_,
this_.COL2 as COL2_20_0_
FROM SomeSchema.SomeView this_
WHERE this_.COL1 = 'SomeValue') as tempresult
where rownum between 10 and 20
The query brings the db server to its knees. I think what's happening is that the nested query is assigning a row number to every row satisfied by the where clause before selecting the subset (rows 10 - 20). Since the nested query will return a lot of rows, the mechanism is not very efficient. I've seen lots of tips and tricks for doing this efficiently on other SQL platforms but I'm struggling to find a DB2 solution. In fact an article on IBM's own site recommends the approach that nhibernate has taken.
Is there a better way?
Unfortunately from what I know it is the only approach. However I think that first you need to identify the time spent on the query components - use visual explain or the snapshot/event monitoring tools.