Hi all



I have a site which uses recordset paging to present data, one client has 

a much larger number of records in one table than I tested with (200,000 

records plus)



My code basically does:-



strsql="Select field1,field2,field3 from jobs"

ors.pagesize

ors.absolutepage=request("pagenumber")

ors.cachesize



The recordset is defined as adOpenStatic and adUseClient.

The rest of the code is the standard ASP stuff to put the fields into a 

html table. The next and previous pages just make re-entrant calls 

increasing or decreasing the pagenumber variable.



The result is unusable as it takes so long to open the script times out 



I know that I can enforce a filter probably so that this only shows live 

records but want to avoid this.



I've seen http://www.adopenstatic.com/experiments/recordsetpaging.asp but 

this doesn't appear to give the answer.



As this is just a browse of the data and does not do any moveprior calls. 

Only sets the absolute page. Should I be using adOpenForward?



I'd appreciate any other pointers if anybody can see a way of doing this. 





Regards

Graham

I assume that you are using SQL Server? If so use a stored procedure, you

will get a performance gain. 



Can they page through the entire 200k records, at 20 at a time? Sounds

strange. Consider using TOP or ROWCOUNT to return say only the 1st 1k.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: gbrown@c... [mailto:gbrown@c...]

Sent: Friday, 24 August 2001 2:39 AM

To: ASP Databases

Subject: [asp_databases] Recordset paging on large recordsets





Hi all



I have a site which uses recordset paging to present data, one client has 

a much larger number of records in one table than I tested with (200,000 

records plus)



My code basically does:-



strsql="Select field1,field2,field3 from jobs"

ors.pagesize

ors.absolutepage=request("pagenumber")

ors.cachesize



The recordset is defined as adOpenStatic and adUseClient.

The rest of the code is the standard ASP stuff to put the fields into a 

html table. The next and previous pages just make re-entrant calls 

increasing or decreasing the pagenumber variable.



The result is unusable as it takes so long to open the script times out 



I know that I can enforce a filter probably so that this only shows live 

records but want to avoid this.



I've seen http://www.adopenstatic.com/experiments/recordsetpaging.asp but 

this doesn't appear to give the answer.



As this is just a browse of the data and does not do any moveprior calls. 

Only sets the absolute page. Should I be using adOpenForward?



I'd appreciate any other pointers if anybody can see a way of doing this. 





Regards

Graham

Hi



I am also using access so it needs to be something that will scale down as 

well.



I thought recordset paging just goes to whatever I've said is absolutepage 

and only fetches the number of records specified by pagesize ie 20 records 

not all 200,000.



Regards

Graham



> 

> I assume that you are using SQL Server? If so use a stored procedure, you

> will get a performance gain. 

> 

> Can they page through the entire 200k records, at 20 at a time? Sounds

> strange. Consider using TOP or ROWCOUNT to return say only the 1st 1k.

> 

> regards

> David Cameron

> nOw.b2b

> dcameron@i...

> 

> -----Original Message-----

> From: gbrown@c... [mailto:gbrown@c...]

> Sent: Friday, 24 August 2001 2:39 AM

> To: ASP Databases

> Subject: [asp_databases] Recordset paging on large recordsets

> 

> 

> Hi all

> 

> I have a site which uses recordset paging to present data, one client 

has 

> a much larger number of records in one table than I tested with (200,000 

> records plus)

> 

> My code basically does:-

> 

> strsql="Select field1,field2,field3 from jobs"

> ors.pagesize

> ors.absolutepage=request("pagenumber")

> ors.cachesize

> 

> The recordset is defined as adOpenStatic and adUseClient.

> The rest of the code is the standard ASP stuff to put the fields into a 

> html table. The next and previous pages just make re-entrant calls 

> increasing or decreasing the pagenumber variable.

> 

> The result is unusable as it takes so long to open the script times out 

> 

> I know that I can enforce a filter probably so that this only shows live 

> records but want to avoid this.

> 

> I've seen http://www.adopenstatic.com/experiments/recordsetpaging.asp 

but 

> this doesn't appear to give the answer.

> 

> As this is just a browse of the data and does not do any moveprior 

calls. 

> Only sets the absolute page. Should I be using adOpenForward?

> 

> I'd appreciate any other pointers if anybody can see a way of doing 

this. 

> 

> 

> Regards

> Graham

> 

> 

Using .AbsolutePage requires an expensive cursor (adOpenStatic etc) - ADO

needs know the size of the whole recordset, so that it can work out where to

put the pages, and where to move to. Assembling this meta information is

expensive.



If you are using SQL Server either use a temp table, or use a simple .move()

command.



If you're using Access/Jet, my testing is that a simple .move plus a

.getRows(numRecs) will beat an adOpenStatic cursor hands down any day (by a

factor of about 3-1, assuming that the database is on the same machine as

the webserver)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <gbrown@c...>

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, August 24, 2001 9:07 AM

Subject: [asp_databases] RE: Recordset paging on large recordsets





: Hi

:

: I am also using access so it needs to be something that will scale down as

: well.

:

: I thought recordset paging just goes to whatever I've said is absolutepage

: and only fetches the number of records specified by pagesize ie 20 records

: not all 200,000.

:

: Regards

: Graham

:

: >

: > I assume that you are using SQL Server? If so use a stored procedure,

you

: > will get a performance gain.

: >

: > Can they page through the entire 200k records, at 20 at a time? Sounds

: > strange. Consider using TOP or ROWCOUNT to return say only the 1st 1k.

: >

: > regards

: > David Cameron

: > nOw.b2b

: > dcameron@i...

: >

: > -----Original Message-----

: > From: gbrown@c... [mailto:gbrown@c...]

: > Sent: Friday, 24 August 2001 2:39 AM

: > To: ASP Databases

: > Subject: [asp_databases] Recordset paging on large recordsets

: >

: >

: > Hi all

: >

: > I have a site which uses recordset paging to present data, one client

: has

: > a much larger number of records in one table than I tested with (200,000

: > records plus)

: >

: > My code basically does:-

: >

: > strsql="Select field1,field2,field3 from jobs"

: > ors.pagesize

: > ors.absolutepage=request("pagenumber")

: > ors.cachesize

: >

: > The recordset is defined as adOpenStatic and adUseClient.

: > The rest of the code is the standard ASP stuff to put the fields into a

: > html table. The next and previous pages just make re-entrant calls

: > increasing or decreasing the pagenumber variable.

: >

: > The result is unusable as it takes so long to open the script times out

: >

: > I know that I can enforce a filter probably so that this only shows live

: > records but want to avoid this.

: >

: > I've seen http://www.adopenstatic.com/experiments/recordsetpaging.asp

: but

: > this doesn't appear to give the answer.

: >

: > As this is just a browse of the data and does not do any moveprior

: calls.

: > Only sets the absolute page. Should I be using adOpenForward?

: >

: > I'd appreciate any other pointers if anybody can see a way of doing

: this.

: >

: >

: > Regards

: > Graham

: >

: >

:

: ---

: * Fast, Full-Featured Microsoft® Excel Web Reports & Charts!

: A breakthrough in high performance Web application development,

SoftArtisans

: ExcelWriter 1.1 supports native Excel charting, image insertion, and

: advanced functions & formatting. One click generates presentation-quality

: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

: Excel Object. Several editions, including ExcelWriterFREE, are available.

: http://www.softartisans.com/softartisans/excelwriter.html>




$subst('Email.Unsub')