Paging article updated

[Aaron Bertrand [MVP]]

This article update was long overdue. There are now 9 different techniques:
3 using ASP alone, and 6 using stored procedures.

[url]http://www.aspfaq.com/2120[/url]

Please let me know if you have any troubles with the examples.

Aaron

[grw]

Nice thanks Aaron!
You dont have a solution for .move / getrows(chunk) available instead of
just getrows()?
From the discussions Ive seen here, thats quite a speedy performer too?



"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:ezdAuB11DHA.1740@TK2MSFTNGP12.phx.gbl...

> This article update was long overdue. There are now 9 different

techniques:

> 3 using ASP alone, and 6 using stored procedures.
>
> [url]http://www.aspfaq.com/2120[/url]
>
> Please let me know if you have any troubles with the examples.
>
> Aaron
>
>

[Aaron Bertrand [MVP]]

> You dont have a solution for .move / getrows(chunk) available instead of

> just getrows()?
> From the discussions Ive seen here, thats quite a speedy performer too?

Actually, it was much worse (unless I've implemented it wrong). I tried 50
runs against each, and here were my timings.

rsGetRows / seek
1.625
1.65625

rsGetRows / move/chunk
20.75
20.78125

Here is the relevant part of how I coded your suggestion:


.... previous was the same
if not rs.eof then
Dim gr
rstop = PerPage * PageNum
rstart = rstop - (PerPage - 1)
chunk = PerPage
' adjust the chunk in case we're on the last page
if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
rs.move(rstart-1)
gr = rs.GetRows(chunk)
response.write "<table border=0 cellpadding=5>"
for i = 0 to perpage - 1
... rest was the same

And I used the same testing page to determine the timings, just upped from
10 to 50 to get a more realistic sample size.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Brynn]

I just spent a lot of time testing the paging script I have been
using.

100 records per page
page # 50
72000 records

if you don't do the .move with several thousand records, it is very
slow.

looping twice

with just getrows = 3.54 seconds
with .move + getrows = .06 seconds

I think that it is the large tables that will create issues if you do
not use the .move method. creating an array with 72000 records may be
the problem.

My cp_DBConn.asp file ... with paging function

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]

This script is currently being tested ... please let me know if you
see any problems with it.





<!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
-->
<%
Dim theArray, thePageNum, recordsPerPage
Dim totalPages, lastRecord

thePage = Request.QueryString("pageNumber"): If thePage = ""
Then thePage = "1"
theNumRecords = 10
sql1 = "select *from counties where state_id = 14;"

Call cp_DBConn("open")
theArray = cp_DBPaging(sql1, thePage, theNumRecords)
Call cp_DBConn("close")



With Response
If Not IsArray(theArray) Then '//if not array then there are
no records
.Write "No Records Found"
Else
totalPages = theArray(0,ubound(theArray,2))
lastRecord = ubound(theArray,2) - 1 '//last record is
just recordcount
lastColumn = ubound(theArray,1)
For n = 0 to lastRecord
For i = 0 to lastColumn
.Write theArray(i,n)
If Not i = lastColumn Then: .Write "
-- ": End If
Next
.Write "<br />"
Next
.Write "<br />"
For n = 1 to totalPages
.Write " <a href=""?pageNumber=" & n & """
style=""color: blue;"""
If CInt(thePage) = CInt(n) Then: .Write "
style=""font-weight: bold; font-size: 20pt;""": End If
.Write ">" & n & "</a> || "
Next
End If
End With
%>


















On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
<aaron@TRASHaspfaq.com> wrote:

>> You dont have a solution for .move / getrows(chunk) available instead of
>> just getrows()?
>> From the discussions Ive seen here, thats quite a speedy performer too?

>
>Actually, it was much worse (unless I've implemented it wrong). I tried 50
>runs against each, and here were my timings.
>
>rsGetRows / seek
> 1.625
> 1.65625
>
>rsGetRows / move/chunk
> 20.75
> 20.78125
>
>Here is the relevant part of how I coded your suggestion:
>
>
>... previous was the same
>if not rs.eof then
> Dim gr
> rstop = PerPage * PageNum
> rstart = rstop - (PerPage - 1)
> chunk = PerPage
> ' adjust the chunk in case we're on the last page
> if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
> rs.move(rstart-1)
> gr = rs.GetRows(chunk)
> response.write "<table border=0 cellpadding=5>"
> for i = 0 to perpage - 1
> ... rest was the same
>
>And I used the same testing page to determine the timings, just upped from
>10 to 50 to get a more realistic sample size.
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>

[Brynn]

update ... change ....

totalPages = theArray(0,ubound(theArray,2))

to

totalPages = cp_TotalPages





On Sun, 11 Jan 2004 07:00:09 GMT, [email]z@z.com[/email] (Brynn) wrote:

>
>I just spent a lot of time testing the paging script I have been
>using.
>
>100 records per page
>page # 50
>72000 records
>
>if you don't do the .move with several thousand records, it is very
>slow.
>
>looping twice
>
>with just getrows = 3.54 seconds
>with .move + getrows = .06 seconds
>
>I think that it is the large tables that will create issues if you do
>not use the .move method. creating an array with 72000 records may be
>the problem.
>
>My cp_DBConn.asp file ... with paging function
>
>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
>This script is currently being tested ... please let me know if you
>see any problems with it.
>
>
>
>
>
><!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>-->
><%
>Dim theArray, thePageNum, recordsPerPage
>Dim totalPages, lastRecord
>
> thePage = Request.QueryString("pageNumber"): If thePage = ""
>Then thePage = "1"
> theNumRecords = 10
> sql1 = "select *from counties where state_id = 14;"
>
> Call cp_DBConn("open")
> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
> Call cp_DBConn("close")
>
>
>
>With Response
> If Not IsArray(theArray) Then '//if not array then there are
>no records
> .Write "No Records Found"
> Else
> totalPages = theArray(0,ubound(theArray,2))
> lastRecord = ubound(theArray,2) - 1 '//last record is
>just recordcount
> lastColumn = ubound(theArray,1)
> For n = 0 to lastRecord
> For i = 0 to lastColumn
> .Write theArray(i,n)
> If Not i = lastColumn Then: .Write "
>-- ": End If
> Next
> .Write "<br />"
> Next
> .Write "<br />"
> For n = 1 to totalPages
> .Write " <a href=""?pageNumber=" & n & """
>style=""color: blue;"""
> If CInt(thePage) = CInt(n) Then: .Write "
>style=""font-weight: bold; font-size: 20pt;""": End If
> .Write ">" & n & "</a> || "
> Next
> End If
>End With
>%>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
><aaron@TRASHaspfaq.com> wrote:
>

>>> You dont have a solution for .move / getrows(chunk) available instead of
>>> just getrows()?
>>> From the discussions Ive seen here, thats quite a speedy performer too?

>>
>>Actually, it was much worse (unless I've implemented it wrong). I tried 50
>>runs against each, and here were my timings.
>>
>>rsGetRows / seek
>> 1.625
>> 1.65625
>>
>>rsGetRows / move/chunk
>> 20.75
>> 20.78125
>>
>>Here is the relevant part of how I coded your suggestion:
>>
>>
>>... previous was the same
>>if not rs.eof then
>> Dim gr
>> rstop = PerPage * PageNum
>> rstart = rstop - (PerPage - 1)
>> chunk = PerPage
>> ' adjust the chunk in case we're on the last page
>> if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>> rs.move(rstart-1)
>> gr = rs.GetRows(chunk)
>> response.write "<table border=0 cellpadding=5>"
>> for i = 0 to perpage - 1
>> ... rest was the same
>>
>>And I used the same testing page to determine the timings, just upped from
>>10 to 50 to get a more realistic sample size.
>>
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>[url]http://www.aspfaq.com/[/url]
>>
>>

>

[Aaron Bertrand [MVP]]

I have no way to judge what's going on. You didn't include the cp_
functions, like cp_DBPaging.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Brynn" <z@z.com> wrote in message
news:4000dbd2.22406338@news.comcast.giganews.com.. .

>
> I just spent a lot of time testing the paging script I have been
> using.
>
> 100 records per page
> page # 50
> 72000 records
>
> if you don't do the .move with several thousand records, it is very
> slow.
>
> looping twice
>
> with just getrows = 3.54 seconds
> with .move + getrows = .06 seconds
>
> I think that it is the large tables that will create issues if you do
> not use the .move method. creating an array with 72000 records may be
> the problem.
>
> My cp_DBConn.asp file ... with paging function
>
> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
> This script is currently being tested ... please let me know if you
> see any problems with it.
>
>
>
>
>
> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
> -->
> <%
> Dim theArray, thePageNum, recordsPerPage
> Dim totalPages, lastRecord
>
> thePage = Request.QueryString("pageNumber"): If thePage = ""
> Then thePage = "1"
> theNumRecords = 10
> sql1 = "select *from counties where state_id = 14;"
>
> Call cp_DBConn("open")
> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
> Call cp_DBConn("close")
>
>
>
> With Response
> If Not IsArray(theArray) Then '//if not array then there are
> no records
> .Write "No Records Found"
> Else
> totalPages = theArray(0,ubound(theArray,2))
> lastRecord = ubound(theArray,2) - 1 '//last record is
> just recordcount
> lastColumn = ubound(theArray,1)
> For n = 0 to lastRecord
> For i = 0 to lastColumn
> .Write theArray(i,n)
> If Not i = lastColumn Then: .Write "
> -- ": End If
> Next
> .Write "<br />"
> Next
> .Write "<br />"
> For n = 1 to totalPages
> .Write " <a href=""?pageNumber=" & n & """
> style=""color: blue;"""
> If CInt(thePage) = CInt(n) Then: .Write "
> style=""font-weight: bold; font-size: 20pt;""": End If
> .Write ">" & n & "</a> || "
> Next
> End If
> End With
> %>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
> <aaron@TRASHaspfaq.com> wrote:
>

> >> You dont have a solution for .move / getrows(chunk) available instead

of

> >> just getrows()?
> >> From the discussions Ive seen here, thats quite a speedy performer too?

> >
> >Actually, it was much worse (unless I've implemented it wrong). I tried

50

> >runs against each, and here were my timings.
> >
> >rsGetRows / seek
> > 1.625
> > 1.65625
> >
> >rsGetRows / move/chunk
> > 20.75
> > 20.78125
> >
> >Here is the relevant part of how I coded your suggestion:
> >
> >
> >... previous was the same
> >if not rs.eof then
> > Dim gr
> > rstop = PerPage * PageNum
> > rstart = rstop - (PerPage - 1)
> > chunk = PerPage
> > ' adjust the chunk in case we're on the last page
> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
> > rs.move(rstart-1)
> > gr = rs.GetRows(chunk)
> > response.write "<table border=0 cellpadding=5>"
> > for i = 0 to perpage - 1
> > ... rest was the same
> >
> >And I used the same testing page to determine the timings, just upped

from

> >10 to 50 to get a more realistic sample size.
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >[url]http://www.aspfaq.com/[/url]
> >
> >

>

[Brynn]

Also...

lastRecord = ubound(theArray,2) - 1

to...

lastRecord = ubound(theArray,2)


On Sun, 11 Jan 2004 07:00:09 GMT, [email]z@z.com[/email] (Brynn) wrote:

>
>I just spent a lot of time testing the paging script I have been
>using.
>
>100 records per page
>page # 50
>72000 records
>
>if you don't do the .move with several thousand records, it is very
>slow.
>
>looping twice
>
>with just getrows = 3.54 seconds
>with .move + getrows = .06 seconds
>
>I think that it is the large tables that will create issues if you do
>not use the .move method. creating an array with 72000 records may be
>the problem.
>
>My cp_DBConn.asp file ... with paging function
>
>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
>This script is currently being tested ... please let me know if you
>see any problems with it.
>
>
>
>
>
><!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>-->
><%
>Dim theArray, thePageNum, recordsPerPage
>Dim totalPages, lastRecord
>
> thePage = Request.QueryString("pageNumber"): If thePage = ""
>Then thePage = "1"
> theNumRecords = 10
> sql1 = "select *from counties where state_id = 14;"
>
> Call cp_DBConn("open")
> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
> Call cp_DBConn("close")
>
>
>
>With Response
> If Not IsArray(theArray) Then '//if not array then there are
>no records
> .Write "No Records Found"
> Else
> totalPages = theArray(0,ubound(theArray,2))
> lastRecord = ubound(theArray,2) - 1 '//last record is
>just recordcount
> lastColumn = ubound(theArray,1)
> For n = 0 to lastRecord
> For i = 0 to lastColumn
> .Write theArray(i,n)
> If Not i = lastColumn Then: .Write "
>-- ": End If
> Next
> .Write "<br />"
> Next
> .Write "<br />"
> For n = 1 to totalPages
> .Write " <a href=""?pageNumber=" & n & """
>style=""color: blue;"""
> If CInt(thePage) = CInt(n) Then: .Write "
>style=""font-weight: bold; font-size: 20pt;""": End If
> .Write ">" & n & "</a> || "
> Next
> End If
>End With
>%>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
><aaron@TRASHaspfaq.com> wrote:
>

>>> You dont have a solution for .move / getrows(chunk) available instead of
>>> just getrows()?
>>> From the discussions Ive seen here, thats quite a speedy performer too?

>>
>>Actually, it was much worse (unless I've implemented it wrong). I tried 50
>>runs against each, and here were my timings.
>>
>>rsGetRows / seek
>> 1.625
>> 1.65625
>>
>>rsGetRows / move/chunk
>> 20.75
>> 20.78125
>>
>>Here is the relevant part of how I coded your suggestion:
>>
>>
>>... previous was the same
>>if not rs.eof then
>> Dim gr
>> rstop = PerPage * PageNum
>> rstart = rstop - (PerPage - 1)
>> chunk = PerPage
>> ' adjust the chunk in case we're on the last page
>> if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>> rs.move(rstart-1)
>> gr = rs.GetRows(chunk)
>> response.write "<table border=0 cellpadding=5>"
>> for i = 0 to perpage - 1
>> ... rest was the same
>>
>>And I used the same testing page to determine the timings, just upped from
>>10 to 50 to get a more realistic sample size.
>>
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>[url]http://www.aspfaq.com/[/url]
>>
>>

>

[Brynn]

I haven't done really extensive testing ... my code is just using the
popular method of .move and getrows ... i have been using this method
from way back when asp101.com first put their article up on getrows.

Anyway, the code I use can be seen, copied, whatever at

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]

but it is just the
rs.move(pageNumber * recsPerPage) - recsPerPage
myArray = rs.GetRows(recsPerPage)

When using it with 72000 records, it seemed to work better. I could be
doing something wrong in my testing ... not sure. It was against a
database of all us zips, cities, couties, states that I have

I doubt a person will end up with 72000 records after they execute
their SQL statement .... but forums and threads can get pretty long
... so can customer databases I guess ... so maybe.

I truly respect your opinions and wisdom, and look forward to your
reply. Just tryin to do the best I can :)

Brynn
[url]www.coolpier.com[/url]





On Sun, 11 Jan 2004 09:41:12 -0500, "Aaron Bertrand [MVP]"
<aaron@TRASHaspfaq.com> wrote:

>I have no way to judge what's going on. You didn't include the cp_
>functions, like cp_DBPaging.
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>
>
>"Brynn" <z@z.com> wrote in message
>news:4000dbd2.22406338@news.comcast.giganews.com. ..

>>
>> I just spent a lot of time testing the paging script I have been
>> using.
>>
>> 100 records per page
>> page # 50
>> 72000 records
>>
>> if you don't do the .move with several thousand records, it is very
>> slow.
>>
>> looping twice
>>
>> with just getrows = 3.54 seconds
>> with .move + getrows = .06 seconds
>>
>> I think that it is the large tables that will create issues if you do
>> not use the .move method. creating an array with 72000 records may be
>> the problem.
>>
>> My cp_DBConn.asp file ... with paging function
>>
>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>>
>> This script is currently being tested ... please let me know if you
>> see any problems with it.
>>
>>
>>
>>
>>
>> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>> -->
>> <%
>> Dim theArray, thePageNum, recordsPerPage
>> Dim totalPages, lastRecord
>>
>> thePage = Request.QueryString("pageNumber"): If thePage = ""
>> Then thePage = "1"
>> theNumRecords = 10
>> sql1 = "select *from counties where state_id = 14;"
>>
>> Call cp_DBConn("open")
>> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
>> Call cp_DBConn("close")
>>
>>
>>
>> With Response
>> If Not IsArray(theArray) Then '//if not array then there are
>> no records
>> .Write "No Records Found"
>> Else
>> totalPages = theArray(0,ubound(theArray,2))
>> lastRecord = ubound(theArray,2) - 1 '//last record is
>> just recordcount
>> lastColumn = ubound(theArray,1)
>> For n = 0 to lastRecord
>> For i = 0 to lastColumn
>> .Write theArray(i,n)
>> If Not i = lastColumn Then: .Write "
>> -- ": End If
>> Next
>> .Write "<br />"
>> Next
>> .Write "<br />"
>> For n = 1 to totalPages
>> .Write " <a href=""?pageNumber=" & n & """
>> style=""color: blue;"""
>> If CInt(thePage) = CInt(n) Then: .Write "
>> style=""font-weight: bold; font-size: 20pt;""": End If
>> .Write ">" & n & "</a> || "
>> Next
>> End If
>> End With
>> %>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
>> <aaron@TRASHaspfaq.com> wrote:
>>

>> >> You dont have a solution for .move / getrows(chunk) available instead

>of

>> >> just getrows()?
>> >> From the discussions Ive seen here, thats quite a speedy performer too?
>> >
>> >Actually, it was much worse (unless I've implemented it wrong). I tried

>50

>> >runs against each, and here were my timings.
>> >
>> >rsGetRows / seek
>> > 1.625
>> > 1.65625
>> >
>> >rsGetRows / move/chunk
>> > 20.75
>> > 20.78125
>> >
>> >Here is the relevant part of how I coded your suggestion:
>> >
>> >
>> >... previous was the same
>> >if not rs.eof then
>> > Dim gr
>> > rstop = PerPage * PageNum
>> > rstart = rstop - (PerPage - 1)
>> > chunk = PerPage
>> > ' adjust the chunk in case we're on the last page
>> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>> > rs.move(rstart-1)
>> > gr = rs.GetRows(chunk)
>> > response.write "<table border=0 cellpadding=5>"
>> > for i = 0 to perpage - 1
>> > ... rest was the same
>> >
>> >And I used the same testing page to determine the timings, just upped

>from

>> >10 to 50 to get a more realistic sample size.
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >[url]http://www.aspfaq.com/[/url]
>> >
>> >

>>

>
>

[Brynn]

P.S. I hope you can appreciate the AGE function test results in the
general group I posted ... it shows what dates will return a bad value
from 1/10/04 current date. And shows why a function like yours is
required.

:)

Brynn



On Sun, 11 Jan 2004 09:41:12 -0500, "Aaron Bertrand [MVP]"
<aaron@TRASHaspfaq.com> wrote:

>I have no way to judge what's going on. You didn't include the cp_
>functions, like cp_DBPaging.
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>
>
>"Brynn" <z@z.com> wrote in message
>news:4000dbd2.22406338@news.comcast.giganews.com. ..

>>
>> I just spent a lot of time testing the paging script I have been
>> using.
>>
>> 100 records per page
>> page # 50
>> 72000 records
>>
>> if you don't do the .move with several thousand records, it is very
>> slow.
>>
>> looping twice
>>
>> with just getrows = 3.54 seconds
>> with .move + getrows = .06 seconds
>>
>> I think that it is the large tables that will create issues if you do
>> not use the .move method. creating an array with 72000 records may be
>> the problem.
>>
>> My cp_DBConn.asp file ... with paging function
>>
>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>>
>> This script is currently being tested ... please let me know if you
>> see any problems with it.
>>
>>
>>
>>
>>
>> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>> -->
>> <%
>> Dim theArray, thePageNum, recordsPerPage
>> Dim totalPages, lastRecord
>>
>> thePage = Request.QueryString("pageNumber"): If thePage = ""
>> Then thePage = "1"
>> theNumRecords = 10
>> sql1 = "select *from counties where state_id = 14;"
>>
>> Call cp_DBConn("open")
>> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
>> Call cp_DBConn("close")
>>
>>
>>
>> With Response
>> If Not IsArray(theArray) Then '//if not array then there are
>> no records
>> .Write "No Records Found"
>> Else
>> totalPages = theArray(0,ubound(theArray,2))
>> lastRecord = ubound(theArray,2) - 1 '//last record is
>> just recordcount
>> lastColumn = ubound(theArray,1)
>> For n = 0 to lastRecord
>> For i = 0 to lastColumn
>> .Write theArray(i,n)
>> If Not i = lastColumn Then: .Write "
>> -- ": End If
>> Next
>> .Write "<br />"
>> Next
>> .Write "<br />"
>> For n = 1 to totalPages
>> .Write " <a href=""?pageNumber=" & n & """
>> style=""color: blue;"""
>> If CInt(thePage) = CInt(n) Then: .Write "
>> style=""font-weight: bold; font-size: 20pt;""": End If
>> .Write ">" & n & "</a> || "
>> Next
>> End If
>> End With
>> %>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
>> <aaron@TRASHaspfaq.com> wrote:
>>

>> >> You dont have a solution for .move / getrows(chunk) available instead

>of

>> >> just getrows()?
>> >> From the discussions Ive seen here, thats quite a speedy performer too?
>> >
>> >Actually, it was much worse (unless I've implemented it wrong). I tried

>50

>> >runs against each, and here were my timings.
>> >
>> >rsGetRows / seek
>> > 1.625
>> > 1.65625
>> >
>> >rsGetRows / move/chunk
>> > 20.75
>> > 20.78125
>> >
>> >Here is the relevant part of how I coded your suggestion:
>> >
>> >
>> >... previous was the same
>> >if not rs.eof then
>> > Dim gr
>> > rstop = PerPage * PageNum
>> > rstart = rstop - (PerPage - 1)
>> > chunk = PerPage
>> > ' adjust the chunk in case we're on the last page
>> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>> > rs.move(rstart-1)
>> > gr = rs.GetRows(chunk)
>> > response.write "<table border=0 cellpadding=5>"
>> > for i = 0 to perpage - 1
>> > ... rest was the same
>> >
>> >And I used the same testing page to determine the timings, just upped

>from

>> >10 to 50 to get a more realistic sample size.
>> >
>> >--
>> >Aaron Bertrand
>> >SQL Server MVP
>> >[url]http://www.aspfaq.com/[/url]
>> >
>> >

>>

>
>

[Brynn]

You can use this to do the paging against a database



<!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
-->
<%
thePage = 2
theNumRecords = 10

sql1 = "select *from counties where state_id = 14;"

Call cp_DBConn("open")
theArray = cp_DBPaging(sql1, thePage, theNumRecords)
Call cp_DBConn("close")
%>






On Sun, 11 Jan 2004 15:31:41 GMT, [email]z@z.com[/email] (Brynn) wrote:

>
>I haven't done really extensive testing ... my code is just using the
>popular method of .move and getrows ... i have been using this method
>from way back when asp101.com first put their article up on getrows.
>
>Anyway, the code I use can be seen, copied, whatever at
>
>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
>but it is just the
>rs.move(pageNumber * recsPerPage) - recsPerPage
>myArray = rs.GetRows(recsPerPage)
>
>When using it with 72000 records, it seemed to work better. I could be
>doing something wrong in my testing ... not sure. It was against a
>database of all us zips, cities, couties, states that I have
>
>I doubt a person will end up with 72000 records after they execute
>their SQL statement .... but forums and threads can get pretty long
>.. so can customer databases I guess ... so maybe.
>
>I truly respect your opinions and wisdom, and look forward to your
>reply. Just tryin to do the best I can :)
>
>Brynn
>[url]www.coolpier.com[/url]
>
>
>
>
>
>On Sun, 11 Jan 2004 09:41:12 -0500, "Aaron Bertrand [MVP]"
><aaron@TRASHaspfaq.com> wrote:
>

>>I have no way to judge what's going on. You didn't include the cp_
>>functions, like cp_DBPaging.
>>
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>[url]http://www.aspfaq.com/[/url]
>>
>>
>>
>>
>>"Brynn" <z@z.com> wrote in message
>>news:4000dbd2.22406338@news.comcast.giganews.com ...

>>>
>>> I just spent a lot of time testing the paging script I have been
>>> using.
>>>
>>> 100 records per page
>>> page # 50
>>> 72000 records
>>>
>>> if you don't do the .move with several thousand records, it is very
>>> slow.
>>>
>>> looping twice
>>>
>>> with just getrows = 3.54 seconds
>>> with .move + getrows = .06 seconds
>>>
>>> I think that it is the large tables that will create issues if you do
>>> not use the .move method. creating an array with 72000 records may be
>>> the problem.
>>>
>>> My cp_DBConn.asp file ... with paging function
>>>
>>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>>>
>>> This script is currently being tested ... please let me know if you
>>> see any problems with it.
>>>
>>>
>>>
>>>
>>>
>>> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>>> -->
>>> <%
>>> Dim theArray, thePageNum, recordsPerPage
>>> Dim totalPages, lastRecord
>>>
>>> thePage = Request.QueryString("pageNumber"): If thePage = ""
>>> Then thePage = "1"
>>> theNumRecords = 10
>>> sql1 = "select *from counties where state_id = 14;"
>>>
>>> Call cp_DBConn("open")
>>> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
>>> Call cp_DBConn("close")
>>>
>>>
>>>
>>> With Response
>>> If Not IsArray(theArray) Then '//if not array then there are
>>> no records
>>> .Write "No Records Found"
>>> Else
>>> totalPages = theArray(0,ubound(theArray,2))
>>> lastRecord = ubound(theArray,2) - 1 '//last record is
>>> just recordcount
>>> lastColumn = ubound(theArray,1)
>>> For n = 0 to lastRecord
>>> For i = 0 to lastColumn
>>> .Write theArray(i,n)
>>> If Not i = lastColumn Then: .Write "
>>> -- ": End If
>>> Next
>>> .Write "<br />"
>>> Next
>>> .Write "<br />"
>>> For n = 1 to totalPages
>>> .Write " <a href=""?pageNumber=" & n & """
>>> style=""color: blue;"""
>>> If CInt(thePage) = CInt(n) Then: .Write "
>>> style=""font-weight: bold; font-size: 20pt;""": End If
>>> .Write ">" & n & "</a> || "
>>> Next
>>> End If
>>> End With
>>> %>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
>>> <aaron@TRASHaspfaq.com> wrote:
>>>
>>> >> You dont have a solution for .move / getrows(chunk) available instead

>>of

>>> >> just getrows()?
>>> >> From the discussions Ive seen here, thats quite a speedy performer too?
>>> >
>>> >Actually, it was much worse (unless I've implemented it wrong). I tried

>>50

>>> >runs against each, and here were my timings.
>>> >
>>> >rsGetRows / seek
>>> > 1.625
>>> > 1.65625
>>> >
>>> >rsGetRows / move/chunk
>>> > 20.75
>>> > 20.78125
>>> >
>>> >Here is the relevant part of how I coded your suggestion:
>>> >
>>> >
>>> >... previous was the same
>>> >if not rs.eof then
>>> > Dim gr
>>> > rstop = PerPage * PageNum
>>> > rstart = rstop - (PerPage - 1)
>>> > chunk = PerPage
>>> > ' adjust the chunk in case we're on the last page
>>> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>>> > rs.move(rstart-1)
>>> > gr = rs.GetRows(chunk)
>>> > response.write "<table border=0 cellpadding=5>"
>>> > for i = 0 to perpage - 1
>>> > ... rest was the same
>>> >
>>> >And I used the same testing page to determine the timings, just upped

>>from

>>> >10 to 50 to get a more realistic sample size.
>>> >
>>> >--
>>> >Aaron Bertrand
>>> >SQL Server MVP
>>> >[url]http://www.aspfaq.com/[/url]
>>> >
>>> >
>>>

>>
>>

>

[Brynn]

ALSO, if curious ... if the recordset is EOF ... IE no records

this is detected in the design by simply checking to see if ...

If IsArray(theArray) Then
'// the design for the recordset
Else
'// No Records Found design or redirect
End If





On Sun, 11 Jan 2004 15:31:41 GMT, [email]z@z.com[/email] (Brynn) wrote:

>
>I haven't done really extensive testing ... my code is just using the
>popular method of .move and getrows ... i have been using this method
>from way back when asp101.com first put their article up on getrows.
>
>Anyway, the code I use can be seen, copied, whatever at
>
>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
>but it is just the
>rs.move(pageNumber * recsPerPage) - recsPerPage
>myArray = rs.GetRows(recsPerPage)
>
>When using it with 72000 records, it seemed to work better. I could be
>doing something wrong in my testing ... not sure. It was against a
>database of all us zips, cities, couties, states that I have
>
>I doubt a person will end up with 72000 records after they execute
>their SQL statement .... but forums and threads can get pretty long
>.. so can customer databases I guess ... so maybe.
>
>I truly respect your opinions and wisdom, and look forward to your
>reply. Just tryin to do the best I can :)
>
>Brynn
>[url]www.coolpier.com[/url]
>
>
>
>
>
>On Sun, 11 Jan 2004 09:41:12 -0500, "Aaron Bertrand [MVP]"
><aaron@TRASHaspfaq.com> wrote:
>

>>I have no way to judge what's going on. You didn't include the cp_
>>functions, like cp_DBPaging.
>>
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>[url]http://www.aspfaq.com/[/url]
>>
>>
>>
>>
>>"Brynn" <z@z.com> wrote in message
>>news:4000dbd2.22406338@news.comcast.giganews.com ...

>>>
>>> I just spent a lot of time testing the paging script I have been
>>> using.
>>>
>>> 100 records per page
>>> page # 50
>>> 72000 records
>>>
>>> if you don't do the .move with several thousand records, it is very
>>> slow.
>>>
>>> looping twice
>>>
>>> with just getrows = 3.54 seconds
>>> with .move + getrows = .06 seconds
>>>
>>> I think that it is the large tables that will create issues if you do
>>> not use the .move method. creating an array with 72000 records may be
>>> the problem.
>>>
>>> My cp_DBConn.asp file ... with paging function
>>>
>>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>>>
>>> This script is currently being tested ... please let me know if you
>>> see any problems with it.
>>>
>>>
>>>
>>>
>>>
>>> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>>> -->
>>> <%
>>> Dim theArray, thePageNum, recordsPerPage
>>> Dim totalPages, lastRecord
>>>
>>> thePage = Request.QueryString("pageNumber"): If thePage = ""
>>> Then thePage = "1"
>>> theNumRecords = 10
>>> sql1 = "select *from counties where state_id = 14;"
>>>
>>> Call cp_DBConn("open")
>>> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
>>> Call cp_DBConn("close")
>>>
>>>
>>>
>>> With Response
>>> If Not IsArray(theArray) Then '//if not array then there are
>>> no records
>>> .Write "No Records Found"
>>> Else
>>> totalPages = theArray(0,ubound(theArray,2))
>>> lastRecord = ubound(theArray,2) - 1 '//last record is
>>> just recordcount
>>> lastColumn = ubound(theArray,1)
>>> For n = 0 to lastRecord
>>> For i = 0 to lastColumn
>>> .Write theArray(i,n)
>>> If Not i = lastColumn Then: .Write "
>>> -- ": End If
>>> Next
>>> .Write "<br />"
>>> Next
>>> .Write "<br />"
>>> For n = 1 to totalPages
>>> .Write " <a href=""?pageNumber=" & n & """
>>> style=""color: blue;"""
>>> If CInt(thePage) = CInt(n) Then: .Write "
>>> style=""font-weight: bold; font-size: 20pt;""": End If
>>> .Write ">" & n & "</a> || "
>>> Next
>>> End If
>>> End With
>>> %>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
>>> <aaron@TRASHaspfaq.com> wrote:
>>>
>>> >> You dont have a solution for .move / getrows(chunk) available instead

>>of

>>> >> just getrows()?
>>> >> From the discussions Ive seen here, thats quite a speedy performer too?
>>> >
>>> >Actually, it was much worse (unless I've implemented it wrong). I tried

>>50

>>> >runs against each, and here were my timings.
>>> >
>>> >rsGetRows / seek
>>> > 1.625
>>> > 1.65625
>>> >
>>> >rsGetRows / move/chunk
>>> > 20.75
>>> > 20.78125
>>> >
>>> >Here is the relevant part of how I coded your suggestion:
>>> >
>>> >
>>> >... previous was the same
>>> >if not rs.eof then
>>> > Dim gr
>>> > rstop = PerPage * PageNum
>>> > rstart = rstop - (PerPage - 1)
>>> > chunk = PerPage
>>> > ' adjust the chunk in case we're on the last page
>>> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>>> > rs.move(rstart-1)
>>> > gr = rs.GetRows(chunk)
>>> > response.write "<table border=0 cellpadding=5>"
>>> > for i = 0 to perpage - 1
>>> > ... rest was the same
>>> >
>>> >And I used the same testing page to determine the timings, just upped

>>from

>>> >10 to 50 to get a more realistic sample size.
>>> >
>>> >--
>>> >Aaron Bertrand
>>> >SQL Server MVP
>>> >[url]http://www.aspfaq.com/[/url]
>>> >
>>> >
>>>

>>
>>

>

[Brynn]

I just ran some tests where the table is 1019732 records big ... and
the total records with my select criteria are 28868 records.

The paging works really fast ...

If I comment out the .move line, and change my getrows to .getrows()
it slows considerably.




On Sun, 11 Jan 2004 15:31:41 GMT, [email]z@z.com[/email] (Brynn) wrote:

>
>I haven't done really extensive testing ... my code is just using the
>popular method of .move and getrows ... i have been using this method
>from way back when asp101.com first put their article up on getrows.
>
>Anyway, the code I use can be seen, copied, whatever at
>
>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>
>but it is just the
>rs.move(pageNumber * recsPerPage) - recsPerPage
>myArray = rs.GetRows(recsPerPage)
>
>When using it with 72000 records, it seemed to work better. I could be
>doing something wrong in my testing ... not sure. It was against a
>database of all us zips, cities, couties, states that I have
>
>I doubt a person will end up with 72000 records after they execute
>their SQL statement .... but forums and threads can get pretty long
>.. so can customer databases I guess ... so maybe.
>
>I truly respect your opinions and wisdom, and look forward to your
>reply. Just tryin to do the best I can :)
>
>Brynn
>[url]www.coolpier.com[/url]
>
>
>
>
>
>On Sun, 11 Jan 2004 09:41:12 -0500, "Aaron Bertrand [MVP]"
><aaron@TRASHaspfaq.com> wrote:
>

>>I have no way to judge what's going on. You didn't include the cp_
>>functions, like cp_DBPaging.
>>
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>[url]http://www.aspfaq.com/[/url]
>>
>>
>>
>>
>>"Brynn" <z@z.com> wrote in message
>>news:4000dbd2.22406338@news.comcast.giganews.com ...

>>>
>>> I just spent a lot of time testing the paging script I have been
>>> using.
>>>
>>> 100 records per page
>>> page # 50
>>> 72000 records
>>>
>>> if you don't do the .move with several thousand records, it is very
>>> slow.
>>>
>>> looping twice
>>>
>>> with just getrows = 3.54 seconds
>>> with .move + getrows = .06 seconds
>>>
>>> I think that it is the large tables that will create issues if you do
>>> not use the .move method. creating an array with 72000 records may be
>>> the problem.
>>>
>>> My cp_DBConn.asp file ... with paging function
>>>
>>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]
>>>
>>> This script is currently being tested ... please let me know if you
>>> see any problems with it.
>>>
>>>
>>>
>>>
>>>
>>> <!-- #include virtual="/coolpier_scripts/_database_tools/DBConn.asp"
>>> -->
>>> <%
>>> Dim theArray, thePageNum, recordsPerPage
>>> Dim totalPages, lastRecord
>>>
>>> thePage = Request.QueryString("pageNumber"): If thePage = ""
>>> Then thePage = "1"
>>> theNumRecords = 10
>>> sql1 = "select *from counties where state_id = 14;"
>>>
>>> Call cp_DBConn("open")
>>> theArray = cp_DBPaging(sql1, thePage, theNumRecords)
>>> Call cp_DBConn("close")
>>>
>>>
>>>
>>> With Response
>>> If Not IsArray(theArray) Then '//if not array then there are
>>> no records
>>> .Write "No Records Found"
>>> Else
>>> totalPages = theArray(0,ubound(theArray,2))
>>> lastRecord = ubound(theArray,2) - 1 '//last record is
>>> just recordcount
>>> lastColumn = ubound(theArray,1)
>>> For n = 0 to lastRecord
>>> For i = 0 to lastColumn
>>> .Write theArray(i,n)
>>> If Not i = lastColumn Then: .Write "
>>> -- ": End If
>>> Next
>>> .Write "<br />"
>>> Next
>>> .Write "<br />"
>>> For n = 1 to totalPages
>>> .Write " <a href=""?pageNumber=" & n & """
>>> style=""color: blue;"""
>>> If CInt(thePage) = CInt(n) Then: .Write "
>>> style=""font-weight: bold; font-size: 20pt;""": End If
>>> .Write ">" & n & "</a> || "
>>> Next
>>> End If
>>> End With
>>> %>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Sat, 10 Jan 2004 22:46:16 -0500, "Aaron Bertrand [MVP]"
>>> <aaron@TRASHaspfaq.com> wrote:
>>>
>>> >> You dont have a solution for .move / getrows(chunk) available instead

>>of

>>> >> just getrows()?
>>> >> From the discussions Ive seen here, thats quite a speedy performer too?
>>> >
>>> >Actually, it was much worse (unless I've implemented it wrong). I tried

>>50

>>> >runs against each, and here were my timings.
>>> >
>>> >rsGetRows / seek
>>> > 1.625
>>> > 1.65625
>>> >
>>> >rsGetRows / move/chunk
>>> > 20.75
>>> > 20.78125
>>> >
>>> >Here is the relevant part of how I coded your suggestion:
>>> >
>>> >
>>> >... previous was the same
>>> >if not rs.eof then
>>> > Dim gr
>>> > rstop = PerPage * PageNum
>>> > rstart = rstop - (PerPage - 1)
>>> > chunk = PerPage
>>> > ' adjust the chunk in case we're on the last page
>>> > if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
>>> > rs.move(rstart-1)
>>> > gr = rs.GetRows(chunk)
>>> > response.write "<table border=0 cellpadding=5>"
>>> > for i = 0 to perpage - 1
>>> > ... rest was the same
>>> >
>>> >And I used the same testing page to determine the timings, just upped

>>from

>>> >10 to 50 to get a more realistic sample size.
>>> >
>>> >--
>>> >Aaron Bertrand
>>> >SQL Server MVP
>>> >[url]http://www.aspfaq.com/[/url]
>>> >
>>> >
>>>

>>
>>

>

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
[url]www.coolpier.com[/url]

[Brynn]

Try out my DBConn.asp script.

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?view=code&file=DBConn.asp[/url]

my site is under complete redesign, so bare with me. I would normally
take it down for something like this, but want to be able to keep some
of my content available.

Brynn
[url]www.coolpier.com[/url]



On Sun, 11 Jan 2004 16:16:37 +1300, "grw" <none@none.com> wrote:

>Nice thanks Aaron!
>You dont have a solution for .move / getrows(chunk) available instead of
>just getrows()?
>From the discussions Ive seen here, thats quite a speedy performer too?
>
>
>
>"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
>news:ezdAuB11DHA.1740@TK2MSFTNGP12.phx.gbl...

>> This article update was long overdue. There are now 9 different

>techniques:

>> 3 using ASP alone, and 6 using stored procedures.
>>
>> [url]http://www.aspfaq.com/2120[/url]
>>
>> Please let me know if you have any troubles with the examples.
>>
>> Aaron
>>
>>

>
>

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
[url]www.coolpier.com[/url]

[Bob Barrows]

Brynn wrote:

> I just ran some tests where the table is 1019732 records big ... and
> the total records with my select criteria are 28868 records.
>
> The paging works really fast ...
>
> If I comment out the .move line, and change my getrows to .getrows()
> it slows considerably.
>

Doesn't that make sense? When you use GetRows(), you're putting ALL the
records into the array, aren't you? Not just the chunk you wanted ...

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Brynn]

I know Bob :) ... we are talking about ...

[url]http://www.aspfaq.com/show.asp?id=2120[/url]

the getrows method used here ...

I am not arguing that Aaron is much better at this stuff than me ... I
just think in the tests, he was using a smaller sized table ... so the
array size really didn't factor in a whole lot.

Commenting those lines in my script is similar to the results of the
getrows method on the above aspfaq.

Brynn

P.S. I really do love your site Aaron :) I remember way back when,
when it first went up :)

On Sun, 11 Jan 2004 11:15:30 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Brynn wrote:

>> I just ran some tests where the table is 1019732 records big ... and
>> the total records with my select criteria are 28868 records.
>>
>> The paging works really fast ...
>>
>> If I comment out the .move line, and change my getrows to .getrows()
>> it slows considerably.
>>

>Doesn't that make sense? When you use GetRows(), you're putting ALL the
>records into the array, aren't you? Not just the chunk you wanted ...
>
>Bob Barrows
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my spam trap so I
>don't check it very often. If you must reply off-line, then remove the
>"NO SPAM"
>
>

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
[url]www.coolpier.com[/url]

[Bob Barrows]

Brynn wrote:

> I know Bob :) ... we are talking about ...
>
> [url]http://www.aspfaq.com/show.asp?id=2120[/url]
>
> the getrows method used here ...
>

Well, here's what it says:
if not rs.eof then

Dim gr
gr = rs.GetRows()

rstop = PerPage * PageNum

So this method is putting all the records into the array.
I do not think I would do it this way. I would use AbsolutePage to move to
the desired page, and then read only the contents of the page into the
GetRows array.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Brynn]

I agree that I would not do it that way. But do you think that the

rs.AbsolutePage / GetRows(recsPerPage)

would have an advantage over

rs.Move(pageNum*recsPerPage) / GetRows(recsPerPage)

in my script ... I would like to know where the benefit or difference
comes in ... I may consider AbsolutePage instead for my own script.


Thanks in advance for your reply Bob :)


On Sun, 11 Jan 2004 12:37:51 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Brynn wrote:

>> I know Bob :) ... we are talking about ...
>>
>> [url]http://www.aspfaq.com/show.asp?id=2120[/url]
>>
>> the getrows method used here ...
>>

>
>Well, here's what it says:
>if not rs.eof then
>
> Dim gr
> gr = rs.GetRows()
>
> rstop = PerPage * PageNum
>
>So this method is putting all the records into the array.
>I do not think I would do it this way. I would use AbsolutePage to move to
>the desired page, and then read only the contents of the page into the
>GetRows array.
>
>Bob Barrows
>
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my spam trap so I
>don't check it very often. If you must reply off-line, then remove the
>"NO SPAM"
>
>

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
[url]www.coolpier.com[/url]

[Brynn]

Let me ammend my most recent question ... to this

Is .AbsolutePage going to be faster (or better) versus .Move() because
with CacheSize iw will lessen the RS load?

Brynn



On Sun, 11 Jan 2004 12:37:51 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Brynn wrote:

>> I know Bob :) ... we are talking about ...
>>
>> [url]http://www.aspfaq.com/show.asp?id=2120[/url]
>>
>> the getrows method used here ...
>>

>
>Well, here's what it says:
>if not rs.eof then
>
> Dim gr
> gr = rs.GetRows()
>
> rstop = PerPage * PageNum
>
>So this method is putting all the records into the array.
>I do not think I would do it this way. I would use AbsolutePage to move to
>the desired page, and then read only the contents of the page into the
>GetRows array.
>
>Bob Barrows
>
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my spam trap so I
>don't check it very often. If you must reply off-line, then remove the
>"NO SPAM"
>
>

I participate in the group to help give examples of code. I do not guarantee the effects of any code posted. Test all code before use!

Brynn
[url]www.coolpier.com[/url]

[Bob Barrows]

Brynn wrote:

> Let me ammend my most recent question ... to this
>
> Is .AbsolutePage going to be faster (or better) versus .Move() because
> with CacheSize iw will lessen the RS load?
>

That's my thinking. I haven't tested it, but that was my thought.
Of course, I would be doing more to limit the records being returned from
the database in the sql statement. I've never done a paging application - I
always prefer to give the users a way to limit the records they wish to see.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[grw]

Must be my testing method, but I get twice the speed with .move
Im not stress testing or anything, just doing a simple serverside JS timer
function.

However, its consistently 16 milliseconds for .move vs 50 milliseconds for
seek
using your script and database.

Running a loop of 10 gives me similar results - 140ms vs 500ms

What gives? :-)

For reference, this is the date function
:

<SCRIPT LANGUAGE=JScript RUNAT=Server>
function y2k(number) {
return (number < 1000) ? number + 1900 : number;
}
function milliDif() {
var d = new Date();
return d.getTime()
}

function elapsedpretty(parm1)
{
var elapsedsecs = 0
var elapsedmins = 0

elapsedsecs=Math.floor(parm1/1000)
parm1=parm1%1000

elapsedmins=Math.floor(elapsedsecs/60)
elapsedsecs=elapsedsecs%60


elapsedpretty=elapsedmins + " minute"
if(elapsedmins!=1)
elapsedpretty=elapsedpretty+"s"

elapsedpretty = elapsedpretty+" " + elapsedsecs+" second"
if(elapsedsecs!=1)
elapsedpretty=elapsedpretty+"s"

elapsedpretty = elapsedpretty+ " "+parm1+" millisecond"
if(parm1!=1)
elapsedpretty=elapsedpretty+"s"

return elapsedpretty;
}
</script>

<%

timeThen = milliDif()

.....code.....

timeNow = milliDif()

elapsed = timeNow - timeThen
strMsg="<br>Process time in ms: " & elapsed & "<br>" &
elapsedpretty(elapsed)
Response.write(strMsg) & "<br><hr>"



"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:O3gm4V$1DHA.3416@tk2msftngp13.phx.gbl...

> > You dont have a solution for .move / getrows(chunk) available instead of
> > just getrows()?
> > From the discussions Ive seen here, thats quite a speedy performer too?

>
> Actually, it was much worse (unless I've implemented it wrong). I tried

50

> runs against each, and here were my timings.
>
> rsGetRows / seek
> 1.625
> 1.65625
>
> rsGetRows / move/chunk
> 20.75
> 20.78125
>
> Here is the relevant part of how I coded your suggestion:
>
>
> ... previous was the same
> if not rs.eof then
> Dim gr
> rstop = PerPage * PageNum
> rstart = rstop - (PerPage - 1)
> chunk = PerPage
> ' adjust the chunk in case we're on the last page
> if rstop > RowCnt - 1 then chunk = (RowCnt - 1) - rstart
> rs.move(rstart-1)
> gr = rs.GetRows(chunk)
> response.write "<table border=0 cellpadding=5>"
> for i = 0 to perpage - 1
> ... rest was the same
>
> And I used the same testing page to determine the timings, just upped from
> 10 to 50 to get a more realistic sample size.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>