Hello,



I'm facing a bit of an issue and I'm hoping some folks out there have

faced the same problem.



I use the ADO recordset paging technique quite a bit. I have a new task

which could potentially result in a recordset of over 100,000 records(it

sounds like a ridcules amount of data, but I'm being over-ruled in this

case)  It probably wouldn't happen, but it could so I have to be prepared

for it.



Using ADO paging the entire recordset gets brought to the client (client

being the webserver) at one time. This could cause some sever bottlenecks,

so it doesn't look like the best solution. For some result sets the ADO

paging technique works like a charm.



I looked into only getting the top x amount of records and every time a

link is clicked, get the next x amount so and so on. The problem with that

method is it requires a sort on a PK. What if I needed to sort the items

by different fields? This technique wouldn't work very well, in fact it

wouldn't work.



So has anyone else encountered a problem like this and may have any

suggestions on the best way to resolve it? (Besides not getting that many

records in the first place).



Any help would be appreciated.



Thanks much



 

You have some options:



a) use server-side keyset cursors and paging. With a keyset cursor only the

primary keys are extracted to start off with. You then move to the record

you want and start fetching. This way, only the records you need are

extracted.



b) Look into using .getRows with two arrays. Start with a tall, but thin

array. Find the IDs that you need, then extract another array - fat but

short, containing all the data for the records you need.



c) Possibly other ways as well, but can't think of them off the top of my

head.



Cheers

Ken



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

From: "James H. Fries" <jhf@w...>

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

Sent: Thursday, January 25, 2001 12:44 PM

Subject: [asp_databases] Huge Result Sets from Query





> Hello,

>

> I'm facing a bit of an issue and I'm hoping some folks out there have

> faced the same problem.

>

> I use the ADO recordset paging technique quite a bit. I have a new task

> which could potentially result in a recordset of over 100,000 records(it

> sounds like a ridcules amount of data, but I'm being over-ruled in this

> case)  It probably wouldn't happen, but it could so I have to be prepared

> for it.

>

> Using ADO paging the entire recordset gets brought to the client (client

> being the webserver) at one time. This could cause some sever bottlenecks,

> so it doesn't look like the best solution. For some result sets the ADO

> paging technique works like a charm.

>

> I looked into only getting the top x amount of records and every time a

> link is clicked, get the next x amount so and so on. The problem with that

> method is it requires a sort on a PK. What if I needed to sort the items

> by different fields? This technique wouldn't work very well, in fact it

> wouldn't work.

>

> So has anyone else encountered a problem like this and may have any

> suggestions on the best way to resolve it? (Besides not getting that many

> records in the first place).

>

> Any help would be appreciated.

>

> Thanks much

There is another thread in this forum called "50 records a time" going on 

that deals with the same issue.

Especially the tips and links given by James H. Fries can be very useful.



Imar





At 01:44 AM 1/25/2001 +0000, you wrote:

>Hello,

>

>I'm facing a bit of an issue and I'm hoping some folks out there have

>faced the same problem.

>

>I use the ADO recordset paging technique quite a bit. I have a new task

>which could potentially result in a recordset of over 100,000 records(it

>sounds like a ridcules amount of data, but I'm being over-ruled in this

>case)  It probably wouldn't happen, but it could so I have to be prepared

>for it.

>

>Using ADO paging the entire recordset gets brought to the client (client

>being the webserver) at one time. This could cause some sever bottlenecks,

>so it doesn't look like the best solution. For some result sets the ADO

>paging technique works like a charm.

>

>I looked into only getting the top x amount of records and every time a

>link is clicked, get the next x amount so and so on. The problem with that

>method is it requires a sort on a PK. What if I needed to sort the items

>by different fields? This technique wouldn't work very well, in fact it

>wouldn't work.

>

>So has anyone else encountered a problem like this and may have any

>suggestions on the best way to resolve it? (Besides not getting that many

>records in the first place).

>

>Any help would be appreciated.

>

>Thanks much

hehehe - Imar, I think it was James that asked the question below - and you

are pointing him to his own posts on another thread? :-)



Cheers

Ken



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

From: "Imar Spaanjaars" <Imar@S...>

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

Sent: Thursday, January 25, 2001 7:26 PM

Subject: [asp_databases] Re: Huge Result Sets from Query





> There is another thread in this forum called "50 records a time" going on

> that deals with the same issue.

> Especially the tips and links given by James H. Fries can be very useful.

>

> Imar

>

>

> At 01:44 AM 1/25/2001 +0000, you wrote:

> >Hello,

> >

> >I'm facing a bit of an issue and I'm hoping some folks out there have

> >faced the same problem.

> >

> >I use the ADO recordset paging technique quite a bit. I have a new task

> >which could potentially result in a recordset of over 100,000 records(it

> >sounds like a ridcules amount of data, but I'm being over-ruled in this

> >case)  It probably wouldn't happen, but it could so I have to be prepared

> >for it.

> >

> >Using ADO paging the entire recordset gets brought to the client (client

> >being the webserver) at one time. This could cause some sever

bottlenecks,

> >so it doesn't look like the best solution. For some result sets the ADO

> >paging technique works like a charm.

> >

> >I looked into only getting the top x amount of records and every time a

> >link is clicked, get the next x amount so and so on. The problem with

that

> >method is it requires a sort on a PK. What if I needed to sort the items

> >by different fields? This technique wouldn't work very well, in fact it

> >wouldn't work.

> >

> >So has anyone else encountered a problem like this and may have any

> >suggestions on the best way to resolve it? (Besides not getting that many

> >records in the first place).

> >

> >Any help would be appreciated.

> >

> >Thanks much

Whooops, you're right, sorry. Still a bit sleepy.





Imar





At 07:33 PM 1/25/2001 +1100, you wrote:

>hehehe - Imar, I think it was James that asked the question below - and you

>are pointing him to his own posts on another thread? :-)

>

>Cheers

>Ken

ah,

I just posted a way to do this on the "50 records a time" thread.

walter



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

From: James H. Fries [mailto:jhf@w...]

Sent: 25 January 2001 01:44

To: ASP Databases

Subject: [asp_databases] Huge Result Sets from Query





Hello,



I'm facing a bit of an issue and I'm hoping some folks out there have

faced the same problem.



I use the ADO recordset paging technique quite a bit. I have a new task

which could potentially result in a recordset of over 100,000 records(it

sounds like a ridcules amount of data, but I'm being over-ruled in this

case)  It probably wouldn't happen, but it could so I have to be prepared

for it.



Using ADO paging the entire recordset gets brought to the client (client

being the webserver) at one time. This could cause some sever bottlenecks,

so it doesn't look like the best solution. For some result sets the ADO

paging technique works like a charm.



I looked into only getting the top x amount of records and every time a

link is clicked, get the next x amount so and so on. The problem with that

method is it requires a sort on a PK. What if I needed to sort the items

by different fields? This technique wouldn't work very well, in fact it

wouldn't work.



So has anyone else encountered a problem like this and may have any

suggestions on the best way to resolve it? (Besides not getting that many

records in the first place).



Any help would be appreciated.



Thanks much



 

Thanks to everyone for their input.



With the potential huge number of records involved, I moved away from the

ADO paging, which could easily cause a bottleneck.



I though of using get rows as well, which is an option that will work, if

incremental chunks of data are stored at a time.



However, I think that getting a limited number of records at a time is the

best solution. I had an issue with sorting, where I was worried about

multiple sort options other than the PK causing my record placement to be

off. I was able to work around this using aliasing and expressions to get

the sort I needed, without sacraficing the order of the PK.



Thanks again everyone.



James

Hi,

   I am trying to solve the same problem.  Do you mind posting your

solution to the list.  Thanks.

-ann

This is a multi-part message in MIME format.



------=_NextPart_000_0016_01C08A35.2F9B98C0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable







Ann,



The original question concerned a query which was returning 1000's

of records. The many respondents such as Imar and Ken suggested

several solutions.  Among these solutions was the use of the recordset

object. The recordset object has several properties which are used in

paged recordsets.  However, it's more complex than can be addressed

in a short emai.  Checkout Lewis Moten's code at the below URL.



http://aspcode.com/display.asp?id=3D542



There is a downloadable zip file which describes how to implement

paging. Personally, I haven't had to deal with paging 1000's of records.

I did implement a paging scheme for a small database using the TOP

modifier in the SELECT statement and a WHERE clause to filter the 

recordset.



Also, checkout the links to the other ASP code sites for additional 

help.



Cheers,

Dallas Martin

















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

From: <awin@a...>

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

Sent: Monday, January 29, 2001 7:15 PM

Subject: [asp_databases] Re: Huge Result Sets from Query





> Hi,

>    I am trying to solve the same problem.  Do you mind posting your

> solution to the list.  Thanks.

> -ann

>

> ---

> FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS

> IN YOUR INBOX!

> Get the latest and best HTML, XML, and JavaScript tips, tools, and

> developments from the experts.  Sign up for one or more of EarthWeb's

> FREE IT newsletters at http://www.earthweb.com today! 




$subst('Email.Unsub')

>