Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    13

    Unanswered: Optimize query for pagination

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Aug 2008
    Posts
    13
    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •