SQL Server Performance

The best way of paging

Discussion in 'Performance Tuning for DBAs' started by Cesar, Mar 7, 2005.

  1. Cesar New Member

    Hi,

    I wrote a Stored Procedure so that in my ASP.NET application the user can navigate through a results page (a data grid) of a database query with two buttons: forward and backward. To do so, this SP uses two variables passed in by the app, @CurrentPage and @PageSize, the first one is the number of the current page the user is seeing the results, and the second one is the number of items (rows) are displayed in every page. These variables are used to return, every time the SP is called, only the number of rows allowed per page, avoiding to return in every page 5000 records only to display 10 of them. To achieve this I use a temporary table in the SP, which I fill every time the user presses ‘next#%92 or ‘previous#%92 button with all the filtered data, and then I return only 10 records.


    Here is the SP:


    USE market
    GO
    ALTER PROCEDURE offers_list
    @CurrentPage int, @PageSize int, @TotalRecords int output
    As

    CREATE TABLE #tempTable
    (
    Id int IDENTITY PRIMARY KEY,
    Offer_id bigint,
    Date datetime,
    Offer_Title varchar(100),
    Company_name varchar(100),
    City_name varchar(100)
    )

    INSERT INTO #tempTable
    (
    Offer_id,
    Date,
    Offer_Title,
    Company_name,
    City_name
    )

    Select Offer_id, Date, Offer_Title, user.Company_name, city.City_name
    From Offers As offe

    JOIN Users As user
    On offe.User_num = user.User_id
    JOIN Cities As city
    On offe.city_num = city.City_id

    Where offer_state = 4
    Order by Date DESC


    Declare @FirstReg int, @LastReg int
    Select @FirstReg = (@CurrentPage - 1) * @PageSize
    Select @LastReg = (@CurrentPage * @PageSize) + 1


    SELECT Offer_id, Date, Offer_Title, Company_name, City_name
    FROM #tempTable
    WHERE
    Id > @FirstReg AND Id < @LastReg

    SELECT @TotalRecords = COUNT(*) FROM #tempTable
    DROP TABLE #tempTable
    GO


    Is this SP an efficient way of paging? Or there are other better alternatives? If not, can I improve this SP performance in some way or it is reasonably good now?

    Thank you,
    Cesar
  2. bambola New Member

    Consider inserting only your PK into the temp table then joining it with your table to retrieve the 10 rows you need.

    Also, if you are using SQL Server 2000, test it with table datatype. with a small rowcount it might perform better (with a big number it will create the temp table anyway).

    BTW, there is no need to run the count on the temp table to get the total records. you can get it using @@rowcount after the insert.
    HTH

    Bambola
  3. Cesar New Member

    Hi Bambola, thank you for your answer [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]. <br /><br />I find very interesting what you said: <i>Also, if you are using SQL Server 2000, test it with table datatype. with a small rowcount it might perform better (with a big number it will create the temp table anyway)</i>. Would you mind giving me an example with my code please? Since I am considering not to use a temporary table, because many users at the same time paging (executing this SP again and again) can affect the performance. So I would like to return every time the corresponding 10 results per page, and also count all the available rows according to the ‘Where#%92 condition in the ‘Select#%92 statement, without using a temporary table. Is this possible? If so, How can I achieve it?<br /><br />Thanks<br />
  4. mmarovic Active Member

    What is logical identifier of rows you return? Is it offer_id or offer_id + date? If you have a few columns that can play role of unique role identifier there is better solution for the problem.<br /><br />First display first page using <pre id="code"><font face="courier" size="2" id="code">select top &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />age size&gt; ... <br />...<br />order by &lt;unique identifier columns starting with date&gt;</font id="code"></pre id="code">When user hits "next" button execute sp assigning its parameters values of columns that are part of unique row key and use again <pre id="code"><font face="courier" size="2" id="code">Select top &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />age size&gt; ... <br />...<br />where &lt;unique identifier values are after saved values&gt; <br />order by &lt;unique identifier columns starting with date&gt;</font id="code"></pre id="code">You also have to have index at least on date column.
  5. Cesar New Member

    I don#%92 t understand it in this way.

    The Identity column is Offer_id, I don#%92 t have any other identifier.
    I am looking for a way in which the SP receives for example @CurrentPage and @PageSize, and with this parameters the SP be able to return ten registers every time it is executed without using a temporary table.

    For example, some idea would be: (I don#%92 t know if it is possible)


    USE market
    GO
    ALTER PROCEDURE offers_list
    @CurrentPage int, @PageSize int, @TotalRecords int output
    As


    -- A: Here count all the rows that are available with this ‘Select#%92 statement:


    Select Offer_id, Date, Offer_Title, user.Company_name, city.City_name
    From Offers As offe

    JOIN Users As user
    On offe.User_num = user.User_id
    JOIN Cities As city
    On offe.city_num = city.City_id

    Where offer_state = 4
    Order by Date DESC

    Declare @Rank_from int, @Rank_to int
    Select @Rank_from = (@CurrentPage - 1) * @PageSize
    Select @Rank_to = (@CurrentPage * @PageSize)


    -- B: Here only return 10 registers based on the @CurrentPage the user is and the @PageSize. For example if user is in page 2, and the page size is 10, return only the counted and found rows from 11 to 20 in section A (@Rank_from = 10, @Rank_to = 20). I mean, if section A has count 2000 registers (according to the Select statement), only return the counted ones from 11 to 20.


    GO


    I don' t know if this is possible, but the idea is return ten records (next or previous) every time like my initial SP does, but without using temporary tables.
  6. mmarovic Active Member

    If I got it right offer_id can't appear twice in result set and you want to walk through results using just "next" and "previous" button.
    What I didn't understand is if you want to display number of pages on GUI.
    If you don't want than you don't need to calculate it at all. All you need is to display current page.
    If user hit "next" button you select top 10 offers ordered by date, offer_id starting from last date and offer_id from the current page.
    If he hits "previous page" button you select top 10 offers ordered by date, offer_id before the first date, offer_id from current page.
    create procedure nextPage
    @CurrentPageDateFromLastRow datetime,
    @CurrentPageOffer_id_FromLastRow int
    as begin
    select top 10 ...
    from ...
    where (Date = @CurrentPageDateFromLastRow and
    Offer_id > @CurrentPageOffer_id_FromLastRow) or
    Date > @CurrentPageDateFromLastRow)
    order by Date, Offer_id
    end
    PreviosPage proc is similar, you just use first row data from the current page...
  7. Cesar New Member

    Yeah.. I see, I think you are right. I am going to test it tomorrow morning, now I am exhausted.. [|)] I' ll explain you how about it.

    Thank you!
  8. Cesar New Member

    Before start trying to write your code, I think you have to explain me why the best websites uses my system of paging, I mean all is update every time the user presses the next or previous button, and when it is reload as well, so if you are in page 2 for 15 minutes and then go to page 3 you can see the same results that in page 2 (because new offers has been added), or you can skip some offers because some were removed.

    I saw a website with 10 new offers every 2 minutes that uses my system of paging, indeed all the websites I saw applies that system, included EBAY!!

    You say:
    “If I got it right offer_id can't appear twice in result set and you want to walk through results using just "next" and "previous" button.”

    Who applies the system of paging you say, taking the last or the first offer id from the current page? I mean, which websites?
  9. mmarovic Active Member

    My answer was based on my understanding of your description of desired process.
    So I think this is more efficient implementation of process you described (at least my understanding of your description at the time being).
    My company (monster.com) applies this technique in some of its applications (e.g. Campaign Management tool).
    However I see your point, functionality would slightly differ from what you originaly wanted. It is up to you to decide which algorithm better fit your needs.
    I believe my proposal is simpler for implementation and with a small improvement I have in mind would be more efficient from performance point of view.
  10. Cesar New Member

    Yes I know [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />I found a code sample that I think does what I am looking for, I mean have the same performance that my initial SP but without using a temporary table. The problem is that I don#%92 t know how to adapt my SP to this other. The technique is called ROWCOUNT.<br /><br />Here is the sample I found in this page:<a target="_blank" href=http://www.aspfaq.com/show.asp?id=2120>http://www.aspfaq.com/show.asp?id=2120</a> <br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE SampleCDs_Paging_Rowcount <br /> @pagenum INT = 1, <br /> @perpage INT = 50 <br />AS <br />BEGIN <br /> SET NOCOUNT ON <br /> <br /> DECLARE <br /> @ubound INT, <br /> @lbound INT, <br /> @pages INT, <br /> @rows INT <br /> <br /> SELECT <br /> @rows = COUNT(*), <br /> @pages = COUNT(*) / @perpage <br /> FROM <br /> SampleCDs WITH (NOLOCK) <br /> <br /> IF @rows % @perpage != 0 SET @pages = @pages + 1 <br /> IF @pagenum &lt; 1 SET @pagenum = 1 <br /> IF @pagenum &gt; @pages SET @pagenum = @pages <br /> <br /> SET @ubound = @perpage * @pagenum <br /> SET @lbound = @ubound - (@perpage - 1) <br /> <br /> SELECT <br /> CurrentPage = @pagenum, <br /> TotalPages = @pages, <br /> TotalRows = @rows <br /> <br /> -- this method determines the string values <br /> -- for the first desired row, then sets the <br /> -- rowcount to get it, plus the next n rows <br /> <br /> DECLARE @aname VARCHAR(64), @title VARCHAR(64) <br /> <br /> SET ROWCOUNT @lbound <br /> <br /> SELECT <br /> @aname = ArtistName, <br /> @title = Title <br /> FROM <br /> SampleCDs WITH (NOLOCK) <br /> ORDER BY <br /> ArtistName, <br /> Title <br /> <br /> SET ROWCOUNT @perPage <br /> <br /> SELECT <br /> ArtistName, <br /> Title <br /> FROM <br /> SampleCDs WITH (NOLOCK) <br /> WHERE <br /> ArtistName + '~' + Title <br /> &gt;= @aname + '~' + @title <br /> ORDER BY <br /> ArtistName, <br /> Title <br /> <br /> SET ROWCOUNT 0 <br />END <br />GO<br /></font id="code"></pre id="code"><br /><br />Do you know how can I adapt my initial SP to this technique? And, what do you think about it?<br /><br />Thank you
  11. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><pre id="code"><font face="courier" size="2" id="code">SET ROWCOUNT @perPage<br />--<br /> SELECT<br /> ArtistName,<br /> Title<br /> FROM<br /> SampleCDs WITH (NOLOCK)<br /> WHERE<br /> ArtistName + '~' + Title<br /> &gt;= @aname + '~' + @title<br /> ORDER BY<br /> ArtistName,<br /> Title</font id="code"></pre id="code"><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">That part is disgusting! That can work well only on SampleCDs db. Table scan will be used each time that select is executed and it would kill performance.<br />However, it is not bad idea, it is actually 'superset' of my solution. First find last row of previous page, then select &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />age size&gt; rows starting with next one.<br />Only difference, solution I suggested expects that row to be passed as a parameter from client and here you calculate it based on total count of rows, page size and page number.<br />To make it work you have to apply condition I mentioned.<br />There is small hack that can be done in order to use index on two columns (date+offer_id) even more efficiently, but let's see first if my explanation was clear enough.
  12. Cesar New Member

    quote:To make it work you have to apply condition I mentioned.

    What condition you mean? It would be simple to adapt my code to this technique?


    I don#%92 t understand how is used:


    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)
    To return the correct rows every time. I know what mean @ubound and @lbound (for example the rank 20 to 11), but I don#%92 t understand how these values are used to return only the correct rows every time the SP is executed.


    I think I only need one index (offer_id) to select the right rows, since it is the Identity.

    Thank you,
    Cesar
  13. mmarovic Active Member

    quote:What condition you mean?
    where (Date = @CurrentPageDateFromLastRow and
    Offer_id > @CurrentPageOffer_id_FromLastRow) or
    Date > @CurrentPageDateFromLastRow)
    quote:I know what mean @ubound and @lbound (for example the rank 20 to 11), but I don#%92 t understand how these values are used to return only the correct rows every time the SP is executed.
    quote:

    SET ROWCOUNT @lbound
    --
    SELECT
    @aname = ArtistName,
    @title = Title
    FROM
    SampleCDs WITH (NOLOCK)
    ORDER BY
    ArtistName,
    Title
    This part select ArtistName and Title from last row of previous page.
    It selects only @lBound rows and assignes values from the last row.

    quote:
    SET ROWCOUNT @perPage
    --
    SELECT
    ArtistName,
    Title
    FROM
    SampleCDs WITH (NOLOCK)
    WHERE
    ArtistName + '~' + Title >= @aname + '~' + @title
    ORDER BY
    ArtistName,
    Title
    Selects @perPage rows starting with first row after last row from the previous page.
    quote:I think I only need one index (offer_id) to select the right rows, since it is the Identity.
    Yes, if you want to order by offer_id. If you want to order by date first then by offer_id, index on date + offer_id is needed.
    In your original proc paging is based on date order.
  14. Cesar New Member

    I tried this:



    USE market
    GO
    ALTER PROCEDURE offers_list
    @CurrentPage int, @PageSize int, @TotalRegisters int output
    As

    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT

    SET @ubound = @PageSize * @CurrentPage
    SET @lbound = @ubound - (@PageSize - 1)




    Declare @Offer_id As bigint

    SET ROWCOUNT @lbound

    Select @Offer_id = Offer_id
    From Offers



    SET ROWCOUNT @PageSize

    Select Offer_id, Date, Offer_title, user.Company_name, city.City_name
    From Offers As offe

    JOIN Users As user
    On offe.User_num = user.User_id
    JOIN Cities As city
    On offe.City_num = city.City_id

    Where Offer_id >= @Offer_id

    SELECT @TotalRegisters = COUNT(*) FROM Offers
    SET ROWCOUNT 0

    END
    GO


    And the result is: When I press next or previous button I always see the same offers. What is wrong?
  15. mmarovic Active Member

    Order by clauses are missing.
  16. Cesar New Member

    If I put 'order by Date Desc' it does the same but ordered by date, I mean I always see the same offers when paging (the last ones inserted, from 1 to 10):



    ...
    SET ROWCOUNT @PageSize
    Select Offer_id, Date, Offer_title, user.Company_name, city.City_name
    From Offers As offe
    JOIN Users As user On offe.User_num = user.User_id
    JOIN Cities As city On offe.City_num = city.City_id
    Where Offer_id >= @Offer_id
    Order by Date Desc

    SELECT @TotalRegisters = COUNT(*) FROM Offers
    SET ROWCOUNT 0
    END
    GO

  17. mmarovic Active Member

    If you have condition taking into account just offer_id you have to order by offer_id. If you want to order by date and offer_id you have to have condition i posted.
  18. Cesar New Member

    If I order by Offer_id it does the same, I think this is not the problem.
    I don' t know what is wrong since with my first SP posted (using temp table) it works perfect. It must be another thing that is wrong in this last SP..
  19. mmarovic Active Member

    Have you added the same order by to both selects? I mean:
    Select @Offer_id = Offer_id
    From Offers
    order by Order_id
    and
    Select Offer_id, Date, Offer_title, user.Company_name, city.City_name
    From Offers As offe
    JOIN Users As user On offe.User_num = user.User_id
    JOIN Cities As city On offe.City_num = city.City_id
    Where Offer_id >= @Offer_id
    Order by Offer_id
    I don't know if behavior used by set rowcount @variable and select @localVariable = column... was ever guaranteed (that it will assign last value from rowset). There is similar solution with set @offer_id = (select top 1 from (select top 10 ...) but if you want page size to be parameter you need dynamic sql.
  20. Cesar New Member

    Mmarovic, What do you think about this system of paging for my case?:


    USE market
    GO
    ALTER PROCEDURE offers_list
    @CurrentPage int, @PageSize int, @TotalItems int output
    AS
    SET NOCOUNT ON

    DECLARE @v_Date DATETIME
    DECLARE @v_FirstOID bigint
    DECLARE @v_LastOID bigint
    DECLARE @v_FirstDate Datetime
    DECLARE @v_LastDate Datetime
    DECLARE @v_MaxRowNumber INT

    SET @v_Date = GETDATE()

    SET @v_MaxRowNumber = @CurrentPage * @PageSize
    SET @TotalItems = 0

    SELECT @TotalItems = @TotalItems + 1
    ,@v_FirstOID = CASE WHEN ((@TotalItems <= @v_MaxRowNumber) AND ((@TotalItems % @PageSize = 1) OR @PageSize = 1)) THEN abt.Offer_id
    ELSE @v_FirstOID
    END
    ,@v_FirstDate = CASE WHEN ((@TotalItems <= @v_MaxRowNumber) AND ((@TotalItems % @PageSize = 1) OR @PageSize = 1)) THEN abt.theDate
    ELSE @v_FirstDate
    END
    ,@v_LastOID = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.Offer_id
    ELSE @v_LastOID
    END
    ,@v_LastDate = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.theDate
    ELSE @v_LastDate
    END
    FROM
    (
    SELECT
    Offer_id, theDate
    FROM Offers AS ofe
    INNER JOIN TheUsers As us On ofe.User_num= us.User_id
    INNER JOIN Cities As city On ofe.city_num = city.City_id
    ) AS abt

    ORDER BY abt.Offer_id ASC



    SELECT Offer_id, theDate, Title, us.Company_name, city.City_name
    FROM Offers AS ofe
    INNER JOIN TheUsers As us On ofe.User_num= us.User_id
    INNER JOIN Cities As city On ofe.city_num = city.City_id
    Where (Offer_id >= @v_FirstOID) AND (Offer_id <= @v_LastOID)
    ORDER BY Offer_id ASC
    GO


    Other suggestions will be welcomed.

    And, How can I order the returned results by theDate (a datetime column) from the most recent Offer to the most old one? For example from 15/03/2005 to 25/07/2004. I only want to order the returned results by theDate, since I want the users can see the Offers inserted in the database by other users from the most recent to the most old ones.

    Thank you,
    Cesar

Share This Page