레코드셋으로 사용자에게 "페이지"를 제공하는 것은 웹 페이지 디자인에서 아주 일반적인 업무이다. 이 것은 각 페이지당 10~50 혹은 그 이상의 고정 값의 줄들을 보여준다는 것이고, 사용자는 다음/이전 또는 페이지 번호를 하단에서 클릭한다는 것이다.
여전에, 이 본문에는 세개의 예제가 있었다. 하나는 ASP로만, 두개는 저장 프로시저로 다른 접근을 했었다.
이제는, 열개의 다른 기술들을 포함하였다. 또한 하나씩 설명한 뒤, (결과에서 볼 수 있는)내 테스트들에서 가장 성능이 좋은 것을 보여 줄 것이고, 모든 예제들(과 테스트한 코드)은 다운로드를 제공한다.
(알파벳 단위를 사용하여 데이터 페이징을 하는 기술에 관심이 있다면..
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.
자, 잡담은 그만하고, 여기 결과값들이 있다.
| Method | Time (seconds)
(Per page) | Time (seconds)
(Per page) | Average |
|---|
Stored Procedure
Rowcount | 0.015000 | 0.016875 | 0.015938 |
Recordset
GetRowsMove | 0.016875 | 0.016875 | 0.016875 |
Recordset
Move | 0.025000 | 0.017500 | 0.021250 |
Stored Procedure
DynamicSQL | 0.021875 | 0.038125 | 0.030000 |
Recordset
GetRowsPage | 0.035000 | 0.033750 | 0.034375 |
Recordset
Page | 0.037500 | 0.033750 | 0.035625 |
Stored Procedure
TempTable | 0.050625 | 0.044375 | 0.047500 |
Stored Procedure
Join | 4.698750 | 2.018125 | 3.358438 |
Stored Procedure
Subquery | 4.191875 | 4.197500 | 4.194688 |
Stored Procedure
UDF | 3.384375 | 6.445625 | 4.915000 |
자, 보는대로다. 페이징 문제에 대한 10가지 접근방법이다. 더 나은 결과치(gospel?)을 얻고 싶다면, 당신의 환경에서 하나씩 시험해 보라. 당신의 하드웨어나, 네트워트 용량, 원격지 데이터베이스에서의 페이지 테스트 여부 등에 따라서, 당신의 결과치는 다르게 나올 것이다. 이 경우, 나는 SQL서버가 같은 서버에 있는 환경에서 ASP 페이지들을 테스트 했고, 실제 운영에 대해서 일부는 고려되지 않은 점이 있다. 하드웨어는 델 워크스테이션(dual 1.4 GHz Xeon processors, 1 GB RAM, IDE drives)을 사용했다. 윈도우즈 서버 2003과 SQL 서버 2000(8.00.818)을 사용했다.
다른 크기의 데이터셋 또한 다른 결과가 나올 수도 있다. 나중에, 이번 것보다 몇배 더 큰 테이블에 같은 테스트들을 해보려한다.
