Optimize query for pagination

Tokajac · #1 (**permalink**) 08-25-08, 11:07

On AS400 database I'm filling pages with the query:

Code:

        SELECT   *
        FROM 
        (
             SELECT * FROM 
            BFWBBBKS b
            left outer join BFWBBCLS c on b.COLID=c.COLID 
            left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID        
            left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
            left outer join BFWBBCAR car on car.CARID =b.CARID
            left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
            left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
                  left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
            order by b.WBBBOOKID
            FETCH FIRST 20 ROWS ONLY 
        ) AS bo
                order by WBBBOOKID desc
        FETCH FIRST 20 ROWS ONLY

For first several pages it is fine, but what's going on last page?
Do U have better idea to solve this? I tried with row_number() but AS400 doesn't recognize this function.

Regards

dav1mo · #2 (**permalink**) 08-25-08, 15:52

Tokajac,
How about explaining what your problem is? You have a query that will return 20 rows in WBBBOOKID order, there is no problem there, but what is it you are looking for?
Dave

Tokajac · #3 (**permalink**) 08-25-08, 16:50

My idea is to take records from database for every page with queries like:

page 1:

Code:

       SELECT   *
        FROM 
        (
             SELECT * FROM 
            BFWBBBKS b
            left outer join BFWBBCLS c on b.COLID=c.COLID 
            left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID        
            left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
            left outer join BFWBBCAR car on car.CARID =b.CARID
            left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
            left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
                  left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
            order by b.WBBBOOKID
            FETCH FIRST 20 ROWS ONLY 
        ) AS bo
                order by WBBBOOKID desc
        FETCH FIRST 20 ROWS ONLY

page 2:

Code:

       SELECT   *
        FROM 
        (
             SELECT * FROM 
            BFWBBBKS b
            left outer join BFWBBCLS c on b.COLID=c.COLID 
            left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID        
            left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
            left outer join BFWBBCAR car on car.CARID =b.CARID
            left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
            left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
                  left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
            order by b.WBBBOOKID
            FETCH FIRST 40 ROWS ONLY 
        ) AS bo
                order by WBBBOOKID desc
        FETCH FIRST 20 ROWS ONLY

page 3:

Code:

       SELECT   *
        FROM 
        (
             SELECT * FROM 
            BFWBBBKS b
            left outer join BFWBBCLS c on b.COLID=c.COLID 
            left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID        
            left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
            left outer join BFWBBCAR car on car.CARID =b.CARID
            left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
            left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
                  left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
            order by b.WBBBOOKID
            FETCH FIRST 60 ROWS ONLY 
        ) AS bo
                order by WBBBOOKID desc
        FETCH FIRST 20 ROWS ONLY

and so on...
There are more than 1000 rows.

Arguments for the the part of query FETCH FIRST x ROWS ONLY would be passed from code.

I want to read only the necessary rows.
This solution brings those rows. But what's going on when I'm on the last page?

Code:

       SELECT   *
        FROM 
        (
             SELECT * FROM 
            BFWBBBKS b
            left outer join BFWBBCLS c on b.COLID=c.COLID 
            left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID        
            left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
            left outer join BFWBBCAR car on car.CARID =b.CARID
            left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
            left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
                  left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
            order by b.WBBBOOKID
            FETCH FIRST 1000 ROWS ONLY 
        ) AS bo
                order by WBBBOOKID desc
        FETCH FIRST 20 ROWS ONLY

Am I reading all the rows and optimization has no sense? Any better idea?

Thank U for your answer, Dave!

Regards

dav1mo · #4 (**permalink**) 08-26-08, 13:00

not sure why you would want to do that. I haven't worked on as/400 as yet, but folks have been executing cursors in some way on those machines for many years without having to resort to what you are suggesting. Though if you really only want to get 20 rows at a time, might I suggest using a host variable and just keep moving your last value into the host variable, then fetch the first 20 rows greater than the last value? The method you describe above is going to keep ordering rows as requested and then throwing away the ones you didn't really want. Also, do you want the same data on mulitple pages? Let's say for the sake of argument that there are only 998 rows today and not 1000. You would go through getting your first 980 rows, then when you get the last set of 20 you will get rows 979 and 980 again to be put on the last page. Have you thought about how you would change the fetch first n rows each time? Are you going to run this as a dynamic cursor, since you can't use a host variable in the fetch clause or do you plan to write that many cursors in your code? Here's an example of the host variable solution that I proposed(first time through it would be low value, then subsequent executions it would have the last value you fetched):
SELECT * FROM
BFWBBBKS b
left outer join BFWBBCLS c on b.COLID=c.COLID
left outer join BFWBBCLA ca on ca.COLADRID=b.COLADRID
left outer join BFWBBCTP ct on ct.CARTPID = b.CARTPID
left outer join BFWBBCAR car on car.CARID =b.CARID
left outer join BFWBBSTS bs on bs.BOOKSTSID = b.BOOKSTSID
left outer join BFWBBUSR cu on CAST(b.CRTUSRID AS INTEGER) = cu.WBBUSERID
left outer join BFWBBUSR uu on CAST(b.UPDBUSRID AS INTEGER) = uu.WBBUSERID
where b.WBBBOOKID > :my-WBBBOOKID
order by b.WBBBOOKID
FETCH FIRST 20 ROWS ONLY

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech