Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Answered: Effective Paging

    I have the joyous task of re-writing some code on our web platform for a product search.

    Essentially I need to end up with a procedure that returns a single page of sorted results given the following vague parameters:
    • @sku varchar(20)
    • @sort_order varchar(20) /* either "SKU" or "Product Name" */
    • @in_stock bit /* 1=In Stock, 0=Out of stock, NULL=everything */
    • @page_number smallint
    • [@items_per_page tinyint = 50]


    I've knocked something together that is more efficient (and accurate) than the current method, but I don't think it is good enough. The biggest challenge I can see if trying to work out the most efficient method of determining the page of results to return.

    The procedure needs to return some details about products and stock levels.
    It also needs to return the total number of records and total page count.

    The DDL, for illustrative purposes, is pretty trivial:
    Code:
    CREATE TABLE dbo.products (
       sku          varchar(20) PRIMARY KEY
     , product_name varchar(255)
     , status       varchar(20)
    );
    
    CREATE TABLE dbo.stock_levels (
       sku         varchar(11) PRIMARY KEY
     , stock_level int CHECK (stock_level >= 0)
    );
    I'm not overly keen on using output parameters for the totals, as this will require greater code re factoring on the application side, so ideally put the totals as derived columns in the output.

    Note, this is for SQL Server 2008 R2, so I don't have OFFSET available

    Thoughts?
    Last edited by gvee; 03-05-15 at 06:15.
    George
    Home | Blog

  2. Best Answer
    Posted by Pat Phelan

    "Since you don't have OFFSET, I think that you'll have to materialize the whole set to paginate it.

    I'd use something like:
    Code:
    DECLARE
       @items_on_page   TINYINT = 5
    ,  @p               INT = 2
    
    ; WITH cte AS (
       SELECT ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS r
    ,     (ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) - 1) / @items_on_page AS page_number
    ,     TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
       )
       SELECT *
          FROM cte
          WHERE @p = page_number
          ORDER BY r
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since you don't have OFFSET, I think that you'll have to materialize the whole set to paginate it.

    I'd use something like:
    Code:
    DECLARE
       @items_on_page   TINYINT = 5
    ,  @p               INT = 2
    
    ; WITH cte AS (
       SELECT ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS r
    ,     (ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) - 1) / @items_on_page AS page_number
    ,     TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
       )
       SELECT *
          FROM cte
          WHERE @p = page_number
          ORDER BY r
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, that's kind of what I have rolled with but it just doesn't perform as well as I would like. Will have to spend some more time tuning it I suppose.

    P.S. I have added something like this to finish it off:
    Code:
    Count(*) OVER (PARTITION BY 937) As total_records
    P.P.S. I like your page_number bit. I went with working out the boundaries of the page (e.g. p2 = 5<r<=10) but like the simplicity of your method
    George
    Home | Blog

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The big advantage that I've found to returning the page number is that it allows you to return N pages (a page range) that the client can then page at the ADO recordset level. This requires a tiny bit of refactoring the client code, but it drastically reduces network/SQL Server traffic with trivial total overhead. It also significantly improves the users perception of the application performance.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Give the application devs as little thinking to do as possible, eh

    Well, after a bit more tweaking and leeching from your suggestion, Pat, I have managed to get the execution time down to an acceptable level. This is without changing any indexing, etc, purely through method changes. Proper indexing is just bonus points!

    Cheers for your help, squire!
    George
    Home | Blog

  7. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can't really post the final code but here's the applicable snippets:

    Step1:
    Code:
           , Row_Number() OVER (ORDER BY CASE WHEN @sorting = 'SKU' THEN sku ELSE product_name END) As sequence
           , Count(*) OVER (PARTITION BY 937) As number_of_records
    Step2:
    Code:
      /* Work out the page numbers and highest possible page number */
           , ((sequence - 1) / @items_per_page) + 1 As page_number
           , Ceiling(number_of_records * 1.0 / @items_per_page) As number_of_pages
    Step3:
    Code:
      /* If the page number requested is greater than the available number of pages, send them the first page! */
      WHERE  page_number = CASE WHEN @page_number BETWEEN 1 AND number_of_pages THEN @page_number ELSE 1 END

    And for the stock levels filter (deliberately verbose for ease of understanding..previously just a pair of case statements in the WHERE clause).
    Code:
    /* Stock level boundaries (low < stock < high) */
    IF @in_stock = 1 -- In stock (stock between 1 and max)
      BEGIN
        SET @stock_level_lower = 0;
        SET @stock_level_upper = 2147483647; -- largest value of int data type
      END
    ELSE IF @in_stock = 0 -- Out of stock (stock between -1 and 1 i.e. zero.)
      BEGIN
        SET @stock_level_lower = -1;
        SET @stock_level_upper = 1;
      END
    ELSE -- Show all! (stock between -1 and max)
      BEGIN
        SET @stock_level_lower = -1;
        SET @stock_level_upper = 2147483647; -- largest value of int data type
      END
    ;
    
    ...
    
    WHERE  stock_level > @stock_level_lower
    AND    stock_level < @stock_level_upper
    George
    Home | Blog

Posting Permissions

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