2016년 6월 23일 목요일

ASP 페이징 구현 관련..

"Recordset.GetRows() + Recordset.Move()" this is fastest way without using stored procedures.

 프로시저 없을시엔 getrows + move 가 가장 성능이 좋다고 함..

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html


레코드셋에서 어떻게 페이지를 나눌까?


레코드셋으로 사용자에게 "페이지"를 제공하는 것은 웹 페이지 디자인에서 아주 일반적인 업무이다. 이 것은 각 페이지당 10~50 혹은 그 이상의 고정 값의 줄들을 보여준다는 것이고, 사용자는 다음/이전 또는 페이지 번호를 하단에서 클릭한다는 것이다.

여전에, 이 본문에는 세개의 예제가 있었다. 하나는 ASP로만, 두개는 저장 프로시저로 다른 접근을 했었다.

Thanks to Chris Hohmann, Anith Sen, Steve Kass, David Portas, Uri Dumant — and, most recently, Brynn and Bob Barrows — for providing me the ammunition and motivation to re-write this article.(저 쉐리들 땜에 다시 글을 작성하게 되서 고맙다는 거임..)
이제는, 열개의 다른 기술들을 포함하였다. 또한 하나씩 설명한 뒤, (결과에서 볼 수 있는)내 테스트들에서 가장 성능이 좋은 것을 보여 줄 것이고, 모든 예제들(과 테스트한 코드)은 다운로드를 제공한다.

(알파벳 단위를 사용하여 데이터 페이징을 하는 기술에 관심이 있다면..Article #2352을 참고하라.)



"페이지 당 줄 수" 문제에 대한 접근방식에는 크게 두가지가 있다: ASP에서의 레코드셋 사용과, 저장 프로시저 사용이다. 두가지 이유로 각각 나눠볼 것이다:(1)Access에서도 사용될 수 있는(WITH (NOLOCK) 힌트를 제거한 후) ASP에서 레코드 셋을 사용하는 코드; 그리고 (2) 일반적으로 사용하는 #include file을 사용할 것이고, 각각의 두가지 경우에 대해서 충분히 실행하고 분리해서 소개해 보겠다.

유콘(Yukon)에서, 새로운 방법으로 이 경우들을 제어해 볼것이다; TOP을 구문에 넣을 것이고, 대량의 자료에서 줄을 "부분적으로" 선택하게 하는 새로운 순위 기능은 훨 씬 쉽게 해 줄 것이다. 현재, SQL 서버 7.0이나 2000을 사용했을 때, 최적화 솔루션 보다 더 작은 부하를 주고 있다.
Recordset Methods

각각의 접근방법에 들어가기 전에, 설정을 하자. 우선 테이블이 필요하다:

CREATE TABLE SampleCDs
(
    ArtistName VARCHAR(64),
    Title VARCHAR(64),
    PRIMARY KEY (ArtistName, Title)
)
GO

채워넣을 데이터도 필요할 것이다. 테이블 생성시 사용한 스크립트는 써놨고, 내 CD 모음집을 넣어보자, here (73 KB).

다음은, ASP 환경에 대한 설정이 필요할 것이다. ASP 페이지들 대부분의 기능들은 일반적인 방법으로 제어 할 수 있고, 우리는 #include files의 장점을 사용할 것이다. 첫번째 파일은 inc.asp 라는 파일을 만들것이고, 연결정보를 갖고 있고 연결 객체(connection object)로 접속한다(물론 HTML 테이블에 사용되는 단일 형식의 선언이다):

<%
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "Provider=SQLOLEDB.1;" & _
        "Network=DBMSSOCN;" & _
        "Data Source=<server>;" & _
        "Initial Catalog=<databasename>;" & _
        "User ID=<username>;" & _
        "Password=<password>"
%>
<style>
.n { border-top:1px solid #999999 }
</style>

To run these individual pages, this should be the only file you have to modify.

The next page is called topRS.asp. It includes logic for obtaining total rowcount from the table, making sure the values passed in (for page number and number of rows per page) are valid, and setting up the dropdown we're going to use to navigate from page to page.

<%
    countSQL = "SELECT COUNT(*) FROM SampleCDs WITH (NOLOCK)"

    RowCnt = 0

    set rs = conn.execute(countSQL)

    if not rs.eof then
        RowCnt = clng(rs(0))
    end if

    if RowCnt = 0 then
    Response.Write "No rows found."
        Response.End
    end if

    PerPage = Trim(Request.QueryString("PerPage"))
    PageNum = Trim(Request.QueryString("PageNum"))

    If PerPage = "" or (len(PerPage)>0 and not isnumeric(PerPage)) Then _
        PerPage = 50

    If PageNum = "" or (len(PageNum)>0 and not isnumeric(PageNum)) Then _
        PageNum = 1

    PerPage = clng(PerPage)
    PageNum = clng(PageNum)

    PageCnt = RowCnt \ PerPage

    if RowCnt mod PerPage <> 0 then PageCnt = PageCnt + 1
    if PageNum < 1 Then PageNum = 1
    if PageNum > PageCnt Then PageNum = PageCnt

    url = Request.ServerVariables("SCRIPT_NAME")
    urlParts = split(url, "/")
    pageName = urlParts(ubound(urlParts))
%>
<script>
function go(p)
{
    if (p!='current')
    window.location.href = "<%=pageName%>?"
        +"PerPage=<%=PerPage%>&"
        +"PageNum="+p;
}
</script>
<%
    response.write RowCnt & " rows found. Showing " & _
        " page " & PageNum & " of " & PageCnt & "."

    response.write " <select onchange='go(this.value);'>"

for i = 1 to PageCnt
        link = i: s = ""
        if i = PageNum then link = "current": s=" SELECTED"
        response.write "<option value=" & link & s & ">"
        response.write "Page " & i
next
Response.Write "</select><p>"
%>

Finally, we have an #include file that cleans up, called foot.asp:

<%
    rs.close: set rs = nothing
    conn.close: set conn = nothing
%>

So, each of our code samples for recordset techniques will look like this:

<!--#include file=inc.asp-->
<!--#include file=topRS.asp-->
<%
    ' ... code ...
%>
<!--#include file=foot.asp-->

Recordset.AbsolutePage
    The first method we'll look at is the AbsolutePage method. This is probably the one you see most often; it uses an explicit ADODB.Recordset object, and properties like PageSize. Of the recordset methods, this one performed the worst in our limited testing (see <a href="<a href="http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html#results">http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html#results</a>" target="_blank" title="#results" style="color: rgb(51, 51, 153); padding-left: 2px; padding-right: 2px;">results</a>).

    <!--#include file=inc.asp-->
    <!--#include file=topRS.asp-->
    <%
        dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"

        set rs = CreateObject("ADODB.Recordset")
        rs.PageSize = PerPage
        rs.CacheSize = PerPage

        ' 3 = adOpenStatic, 1 = adLockReadOnly, &H0001 = adCmdText
        rs.Open dataSQL, conn, 3, 1, &H0001

        if not rs.eof then

            response.write "<table border=0 cellpadding=5>"

            rs.AbsolutePage = PageNum

            x = 0
            do while x < PerPage and not rs.eof
               
                artist = rs(0)
                title = rs(1)

                if artist <> prevArtist then
                    prevArtist = artist
                    response.write "<tr><td class=n>"
                    response.write artist & "</td>"
                    response.write "<td class=n>"
                else
                    response.write "<tr><td> </td><td>"
                end if

                response.write title & "</td></tr>"

                x = x + 1

                rs.movenext
            loop

            response.write "</table>"
        else
            response.write "No rows found."
            response.end
        end if
    %>
    <!--#include file=foot.asp-->

    This code can be found in rsPage.asp.
Recordset.GetRows() + AbsolutePage
    Most people would probably assume that GetRows() would perform quite well, but it only performed slightly better than the AbsolutePage method described above. GetRows() converts a heavy recordset object into a lighter-weight array for local processing (see <a href="<a href="http://www.aspfaq.com/show.asp?id=2467">http://www.aspfaq.com/show.asp?id=2467</a>" target="_self" title="<a href="http://www.aspfaq.com/show.asp?id=2467">http://www.aspfaq.com/show.asp?id=2467</a>" style="color: rgb(51, 51, 153); padding-left: 2px; padding-right: 2px;">Article #2467</a> for more information); though, in order to reduce the size of the array, we use AbsolutePage and PageSize to move right to the relevant chunk of rows.

    <!--#include file=inc.asp-->
    <!--#include file=topRS.asp-->
    <%
        dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"

        set rs = CreateObject("ADODB.Recordset")
        rs.PageSize = PerPage
        rs.CacheSize = PerPage

        ' 3 = adOpenStatic, 1 = adLockReadOnly, &H0001 = adCmdText
        rs.Open dataSQL, conn, 3, 1, &H0001

        if not rs.eof then    

            rs.AbsolutePage = PageNum

            Dim gr
            gr = rs.GetRows(PerPage)

            response.write "<table border=0 cellpadding=5>"

            for i = 0 to perpage - 1
               
                artist = gr(0, i)
                title = gr(1, i)

                if artist <> prevArtist then
                    prevArtist = artist
                    response.write "<tr><td class=n>"
                    response.write artist & "</td>"
                    response.write "<td class=n>"
                else
                    response.write "<tr><td> </td><td>"
                end if

                response.write title & "</td></tr>"

            next

            response.write "</table>"
        else
            response.write "No rows found."
            response.end
        end if
    %>
    <!--#include file=foot.asp-->

    This code can be found in rsGetRowsPage.asp.
Recordset.Move()
    The Recordset.Move() technique was the first example I ever posted in this article. In an attempt to eliminate the need for heavy recordset objects, I decided to try the Move() method to skip the first n rows in the resultset to start at the first row for the page we are interested in.

    <!--#include file=inc.asp-->
    <!--#include file=topRS.asp-->
    <%
    rstart = PerPage * Pagenum - (PerPage - 1)

        dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"

        set rs = conn.execute(dataSQL)

        if not rs.eof then

            rs.move(rstart-1)

            response.write "<table border=0 cellpadding=5>"

            for x = 1 to PerPage

                if rs.eof then exit for

                artist = rs(0)
                title = rs(1)

                if artist <> prevArtist then
                    prevArtist = artist
                    response.write "<tr><td class=n>"
                    response.write artist & "</td>"
                    response.write "<td class=n>"
                else
                    response.write "<tr><td> </td><td>"
                end if

                response.write title & "</td></tr>"

                rs.movenext
            next

            response.write "</table>"
        else
            response.write "No rows found."
            response.end
        end if
    %>
    <!--#include file=foot.asp-->

    This code can be found in rsMove.asp.
Recordset.GetRows() + Recordset.Move()
    This method combines the effective move() technique, with GetRows() ability to accept a "chunk" as a parameter. Of the recordset technologies we tested, this performed best.

    <!--#include file=inc.asp-->
    <!--#include file=topRS.asp-->
    <%
        dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)"

        set rs = conn.execute(dataSQL)

        if not rs.eof then    

            Dim gr
            rstop = PerPage * PageNum
            rstart = rstop - (PerPage - 1)
            rs.move(rstart-1)

            if rstop > RowCnt - 1 then PerPage = (RowCnt + 1) - rstart
            gr = rs.GetRows(PerPage)

            response.write "<table border=0 cellpadding=5>"

            for i = 0 to perpage-1
               
                artist = gr(0, i)
                title = gr(1, i)

                if artist <> prevArtist then
                    prevArtist = artist
                    response.write "<tr><td class=n>"
                    response.write artist & "</td>"
                    response.write "<td class=n>"
                else
                    response.write "<tr><td> </td><td>"
                end if

                response.write title & "</td></tr>"

            next

            response.write "</table>"
        else
            response.write "No rows found."
            response.end
        end if
    %>
    <!--#include file=foot.asp-->

    This code can be found in rsGetRowsMove.asp.


Stored Procedure Methods

If you are using Access, you could probably enhance a paging solution above to use stored queries. However, stored queries are not quite as flexible and powerful as stored procedures in SQL Server. One of the main drawbacks of the recordset methods described above is that *all* the rows are requested from the database and sent over the network to the ASP page. It is only at the web server that the bulk of the resultset is discarded, and this can be quite a wasteful operation on larger tables.

I experimented with six different techniques in stored procedures to carve up a resultset into pages of user-defined row counts. A couple are my own design, one came from Chris Hohmann, and a couple were derived from conversations in the SQL Server newsgroups.

Like with the recordset methods, before we dive into the stored procedure techniques, we need to set up a few things. First will be the ASP pages. As before, we have inc.asp (for connection information) and foot.asp (for cleanup). Instead of topRS.asp, we have topSP.asp. This file is moderately different; the main difference being that we no longer need a query to determine total number of rows / pages, since we will get that from the stored procedure each time. Here is topSP.asp:

<%
    PerPage = Trim(Request.QueryString("PerPage"))
    PageNum = Trim(Request.QueryString("PageNum"))

    If PerPage = "" or (len(PerPage)>0 and not isnumeric(PerPage)) Then _
        PerPage = 50

    If PageNum = "" or (len(PageNum)>0 and not isnumeric(PageNum)) Then _
        PageNum = 1

    PerPage = clng(PerPage)
    PageNum = clng(PageNum)

    url = Request.ServerVariables("SCRIPT_NAME")
    urlParts = split(url, "/")
    pageName = urlParts(ubound(urlParts))
%>
<script>
function go(p)
{
    if (p!='current')
    window.location.href = "<%=pageName%>?"
        +"PerPage=<%=PerPage%>&"
        +"PageNum="+p;
}
</script>
<%
    spName = "SampleCDs_Paging_" & Mid(Left(pageName, Len(pageName)-4), 3, 32)
    dataSQL = "EXEC " & spName & " " & pageNum & ", " & perPage

    set rs = conn.execute(dataSQL)

    PageNum = rs(0)
    PageCnt = rs(1)
    RowCnt = rs(2)

    if RowCnt = 0 then
        response.write "No rows found."
        response.end
    end if

    response.write RowCnt & " rows found. Showing " & _
        " page " & PageNum & " of " & PageCnt & "."

    response.write " <select onchange='go(this.value);'>"

for i = 1 to PageCnt
        link = i: s = ""
        if i = PageNum then link = "current": s=" SELECTED"
        response.write "<option value=" & link & s & ">"
        response.write "Page " & i
next
Response.Write "</select><p>"
%>

Notice that I named the stored procedures in a manner consistent with the name of the ASP pages that call them, respectively. This way, we can use the name of the page to call the stored procedure, instead of hard-coding it into six different files.

Now, the ASP code used to call each stored procedure is identical. So for each method below, you will be asked to create an ASP file with a specific name, using the "common ASP code from above." That code is as follows:

<!--#include file=inc.asp-->
<!--#include file=topSP.asp-->
<%
    set rs = rs.NextRecordset()

    if not rs.eof then    

        Dim gr
        gr = rs.GetRows()

        rstop = PerPage - 1

        if rstop > ubound(gr, 2) then rstop = ubound(gr, 2)

        response.write "<table border=0 cellpadding=5>"

        for i = 0 to rstop
           
            artist = gr(0, i)
            title = gr(1, i)

            if artist <> prevArtist then
                prevArtist = artist
                response.write "<tr><td class=n>"
                response.write artist & "</td>"
                response.write "<td class=n>"
            else
                response.write "<tr><td> </td><td>"
            end if

            response.write title & "</td></tr>"

        next

        response.write "</table>"
    else
        response.write "No rows found."
        response.end
    end if

%>
<!--#include file=foot.asp-->

We use GetRows() here not because it has any particular impact on any of the methods we chose, but rather because we know that this is *usually* the most efficient method of displaying a resultset. Rather than get into another comparison for the different display techniques, I chose to just process all the stored procedures using GetRows() to eliminate that as a source for discrepancy during performance testing. We don't really need to use the extended capabilities above (AbsolutePage, PageSize, Move(), and the "chunk" parameter) because the stored procedure is already reducing the resultset to exactly the size of slice we want.

User-Defined Function
    A UDF is basically an encapsulation of code that returns a table or a scalar value. In this case, I created a function that returns an integer, representing the "rank" of a given artist/title combination, alphabetically, compared to the rest of the table. In individual testing, when viewing the execution plan in Query Analyzer, this method seemed to do quite well. However, it did not fare so well when put to a heavier test (see <a href="<a href="http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html#results">http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html#results</a>" target="_blank" title="#results" style="color: rgb(51, 51, 153); padding-left: 2px; padding-right: 2px;">results</a>). The run time roughly doubled when dealing with rows near the "end" of the table; a good indicator of a well-implemented paging solution is little or no difference between obtaining the first page and the last page.

    Here is the function code:

    CREATE FUNCTION dbo.ReturnCDRank
    (
        @ArtistName VARCHAR(64),
        @Title VARCHAR(64)
    )
    RETURNS INT
    AS
    BEGIN
        RETURN
        (
            SELECT
                COUNT(*)
            FROM
                SampleCDs WITH (NOLOCK)
            WHERE
                ArtistName + '~' + Title
                <= @ArtistName + '~' + @Title
        )
    END
    GO

    And here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_UDF
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0 SET @pages = @pages + 1
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method uses a user-defined function
        -- to rank the rows based on how many rows
        -- exist with "lower" string values

        SELECT
            ArtistName,
            Title
        FROM
            SampleCDs WITH (NOLOCK)
        WHERE
            dbo.ReturnCDRank(ArtistName, Title)
        BETWEEN
            @lbound AND @ubound
        ORDER BY
            ArtistName,
            Title
    END
    GO

    To run this page, create a file called spUDF.asp, and include the common ASP code from above.
     
Subquery / COUNT
    Another method I played with is a subquery with a count. This uses a COUNT of rows in a subquery where the values are compared to the outer table. Like the UDF, the performance of this solution was unacceptable, though the run time didn't change much at either end of the result set.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Subquery
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0 SET @pages = @pages + 1
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method uses a COUNT subquery to
        -- peg a sliding range to the desired set
        -- of rows

        SELECT
            A.ArtistName,
            A.Title
        FROM
            SampleCDs A WITH (NOLOCK)
        WHERE
        (
            SELECT COUNT(*)
            FROM SampleCDs B WITH (NOLOCK)
            WHERE B.ArtistName+'~'+B.Title
            <= A.ArtistName+'~'+A.Title
        )
        BETWEEN
            @lbound AND @ubound
        ORDER BY
            A.ArtistName,
            A.Title
    END
    GO

    To run this page, create a file called spSubquery.asp, and include the common ASP code from above.
     
Self-Join / COUNT
    This technique is only a little bit different from the subquery / COUNT technique, using a self-join instead to determine the ranking of the relevant rows. It performed a little better overall, and surprisingly, was better individually on later pages than earlier pages.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Join
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0 SET @pages = @pages + 1
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method uses an inner join and a
        -- having clause to move a sliding window
        -- to the desired set of rows

        SELECT
            A.ArtistName,
            A.Title
        FROM
            SampleCDs A WITH (NOLOCK)
            INNER JOIN SampleCDs B WITH (NOLOCK)
        ON
            A.ArtistName+'~'+A.Title >= B.ArtistName+'~'+B.Title
        GROUP BY
            A.ArtistName, A.Title
        HAVING
            COUNT(*) BETWEEN @lbound AND @ubound
        ORDER BY
            A.ArtistName,
            A.Title

    END
    GO

    To run this page, create a file called spJoin.asp, and include the common ASP code from above.
     
#Temp table
    This was the first stored procedure example I posted to this article, though it is cleaned up a bit now. The code inserts the entire base table into a #temp table with an IDENTITY column, then uses that column to determine "rank." Note that the behavior we see here is taken for granted; it is neither documented nor guaranteed to obey the ORDER BY clause when inserting the rows into the #temp table. The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. Luckily, it just so happens that it works right so much more often than it fails, that it is virtually reliable. Still, it should be noted with an asterisk, as the behavior could change in future versions.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_TempTable
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0 SET @pages = @pages + 1
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method inserts rows into a temp
        -- table, and the identity helps us rank
        -- them and return only the desired rows

        CREATE TABLE #CDList
        (
            CDID INT IDENTITY(1,1)
            PRIMARY KEY CLUSTERED,
            ArtistName VARCHAR(64),
            Title VARCHAR(64)
        )
       
        -- behavior not guaranteed!
        INSERT #CDList
            SELECT
                ArtistName,
                Title
            FROM
                SampleCDs WITH (NOLOCK)
            ORDER BY
                ArtistName,
                Title

        SELECT
            ArtistName,
            Title
        FROM
            #CDList
        WHERE
            CDID
        BETWEEN
            @lbound AND @ubound
        ORDER BY
            ArtistName,
            Title

        DROP TABLE #CDList
    END
    GO

    To run this page, create a file called spTempTable.asp, and include the common ASP code from above.
     
Dynamic SQL
    This is basically the second stored procedure method I posted to this article (and not too long ago, I might add). It uses nested TOP commands to get the "last" set of rows from a larger set that is basically the first @perpage * @pagenum rows. So, if we had 10 rows per page, and we were on page 8, we would be asking for:

    SELECT TOP 10 rows FROM (SELECT TOP 80 rows ORDER BY ASC) ORDER BY DESC

    (I'm probably not explaining that very well; my only excuse is that it's late.)

    Then we wrap another query around it so we can still present the rows in ASCENDING order. The only reason we have to use dynamic SQL here is because the TOP command cannot take a variable. (See <a href="<a href="http://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.se/dynamic_sql.html</a>" target="_blank" title="<a href="http://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.se/dynamic_sql.html</a>" style="color: rgb(51, 51, 153); padding-left: 2px; padding-right: 2px;">this article</a> for more information on dynamic SQL.)

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_DynamicSQL
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT,
            @lastPageDiff INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0
            SET @pages = @pages + 1
        IF @rows % @perpage != 0 AND @pagenum = @pages
            SET @lastPageDiff = @perPage - (@rows % @perpage)
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method uses dynamic SQL (because TOP
        -- can't take a parameter in SQL Server 2000
        -- and lower) to retrieve a nested subquery

        DECLARE @sql NVARCHAR(1024)

        SET @sql = N'SELECT
                ArtistName,
                Title
            FROM
            (
                SELECT TOP '+RTRIM(@perpage - @lastPageDiff)
                    +' ArtistName,
                    Title
                FROM
                (
                    SELECT TOP '+RTRIM(@perpage * @pagenum)
                        +' ArtistName,
                        Title
                    FROM
                        SampleCDs WITH (NOLOCK)
                    ORDER BY
                        ArtistName+''~''+Title
                ) a
                ORDER BY
                    ArtistName+''~''+Title DESC
            ) b
            ORDER BY
                ArtistName+''~''+Title'

        EXEC sp_executeSQL @sql

    END
    GO

    To run this page, create a file called spTempTable.asp, and include the common ASP code from above.
     
RowCount
    This was Chris Hohmann's gem, and I only touched it up a little bit to fit this example. Basically, it uses SET ROWCOUNT to assign the *first* value from the current page to local variables. Then it sets ROWCOUNT again to the value representing the number of rows we want to see per page, and asks for all the rows >= the value of the local variables. Altogether genius, and it was the best performer of the day as well.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Rowcount
        @pagenum INT = 1,
        @perpage INT = 50
    AS
    BEGIN
        SET NOCOUNT ON

        DECLARE
            @ubound INT,
            @lbound INT,
            @pages INT,
            @rows INT

        SELECT
            @rows = COUNT(*),
            @pages = COUNT(*) / @perpage
        FROM
            SampleCDs WITH (NOLOCK)

        IF @rows % @perpage != 0 SET @pages = @pages + 1
        IF @pagenum < 1 SET @pagenum = 1
        IF @pagenum > @pages SET @pagenum = @pages

        SET @ubound = @perpage * @pagenum
        SET @lbound = @ubound - (@perpage - 1)

        SELECT
            CurrentPage = @pagenum,
            TotalPages = @pages,
            TotalRows = @rows

        -- this method determines the string values
        -- for the first desired row, then sets the
        -- rowcount to get it, plus the next n rows

        DECLARE @aname VARCHAR(64), @title VARCHAR(64)

        SET ROWCOUNT @lbound

        SELECT
            @aname = ArtistName,
            @title = Title
        FROM
            SampleCDs WITH (NOLOCK)
        ORDER BY
            ArtistName,
            Title

        SET ROWCOUNT @perPage

        SELECT
            ArtistName,
            Title
        FROM
            SampleCDs WITH (NOLOCK)
        WHERE
            ArtistName + '~' + Title
            >= @aname + '~' + @title
        ORDER BY
            ArtistName,
            Title

        SET ROWCOUNT 0
    END
    GO

    To run this page, create a file called spRowcount.asp, and include the common ASP code from above.


Results

I used a looping script to hit each of the 9 ASP pages 20 times. 10 times where it would get the first page, and 10 times where it would get the last page (in this case, I broke the results up 20 rows at a time, and asked for the last page (64)). Here is the ASP code used in the test:

<%

    numberOfTries = 10

    server.scripttimeout = 300000

    dim pn(9)

    pn(0) = "rsMove"
    pn(1) = "rsPage"
    pn(2) = "rsGetRowsMove"
    pn(3) = "rsGetRowsPage"
    pn(4) = "spRowcount"
    pn(5) = "spDynamicSQL"
    pn(6) = "spSubquery"
    pn(7) = "spJoin"
    pn(8) = "spUDF"
    pn(9) = "spTempTable"

    set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    originalClock = timer
    response.write originalClock & "<P>"

    for i = 0 to 9

        originalClock = timer
        for x = 1 to numberOfTries
            url = "http://www.aaronbertrand.com/pg/" & _
                pn(i) & ".asp"
            xmlhttp.open "GET", url, false
            xmlhttp.send ""
            result = xmlhttp.ResponseText
        next
        newclock = timer
        response.write pn(i) & ": " & newclock & _
            " (" & newclock - originalClock & ")<br>"
        originalClock = newCLock

        for x = 1 to numberOfTries
            url = "http://www.aaronbertrand.com/pg/" & _
                pn(i) & ".asp?PageNum=62&PerPage=20"
            xmlhttp.open "GET", url, false
            xmlhttp.send ""
            result = xmlhttp.ResponseText
        next
        newclock = timer
        response.write pn(i) & ": " & newclock & _
            " (" & newclock - originalClock & ")<br>"
        originalClock = newCLock
    next
    response.write "<P>" & NewClock
    set xmlhttp = nothing
%>

I assign the ResponseText to a result, even though it is never used, to make sure I simulate the drawing of data down to the browser; even though the rendering is missing, it makes the performance truer than never asking for the responseText.
브라우저에 데이터를 표시하는 것을 가정하기 위해서, 결과에 전혀 사용하지는 않았지만,  ResponseText(응답 텍스트)를 할당하였다; 렌더링시에 누락이 있긴 했지만, 응답 텍스트를 전혀 요청하지 않는 것 보다 더 성능이 좋았다.

Okay, enough with the chit-chat. Here are the results.
자, 잡담은 그만하고, 여기 결과값들이 있다.

MethodTime (seconds)
(Per page)		Time (seconds)
(Per page)		Average
Stored Procedure
Rowcount		0.0150000.0168750.015938
Recordset
GetRowsMove		0.0168750.0168750.016875
Recordset
Move		0.0250000.0175000.021250
Stored Procedure
DynamicSQL		0.0218750.0381250.030000
Recordset
GetRowsPage		0.0350000.0337500.034375
Recordset
Page		0.0375000.0337500.035625
Stored Procedure
TempTable		0.0506250.0443750.047500
Stored Procedure
Join		4.6987502.0181253.358438
Stored Procedure
Subquery		4.1918754.1975004.194688
Stored Procedure
UDF		3.3843756.4456254.915000

자, 보는대로다. 페이징 문제에 대한 10가지 접근방법이다. 더 나은 결과치(gospel?)을 얻고 싶다면, 당신의 환경에서 하나씩 시험해 보라. 당신의 하드웨어나, 네트워트 용량, 원격지 데이터베이스에서의 페이지 테스트 여부 등에 따라서, 당신의 결과치는 다르게 나올 것이다. 이 경우, 나는 SQL서버가 같은 서버에 있는 환경에서 ASP 페이지들을 테스트 했고, 실제 운영에 대해서 일부는 고려되지 않은 점이 있다. 하드웨어는 델 워크스테이션(dual 1.4 GHz Xeon processors, 1 GB RAM, IDE drives)을 사용했다. 윈도우즈 서버 2003과 SQL 서버 2000(8.00.818)을 사용했다.

다른 크기의 데이터셋 또한 다른 결과가 나올 수도 있다. 나중에, 이번 것보다 몇배 더 큰 테이블에 같은 테스트들을 해보려한다.
You can find all of the files used in this article in our downloads section.
시간:
라벨: ,

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)

BE Band (비밴드) - 2024년 03월 02일 잠실새내 락앤롤욱스 공연

나의 10~20대를 보낸 잠실에서의 공연.. 오랜만에 가보니.. 여기가.. 마눌님과 자주 가던 영화관이었는데... 여긴 뭐가 있었는데... 란 추억도 떠올리며 기분좋게 감.​ 공연장은 좀 협소한 편이었고, 인천의 쥐똥나무 보다는 약간 크고... 인천 ...