Rendering large recordsets

[Ken McAndrew]

I'm trying to work on some performance issues in a couple of areas. The
scenario is that I have an 8000+ recordset that's returned and intended to
be written to a drop-down list. This can take up to 10 minutes or more to
occur. I already have the query in a stored procedure, and I've set up
disconnected recordsets to try and help as well. With buffering on, there
doesn't appear to be any action happening, though I'm considering using
Response.Flush to force a render every 100 records or so, so it doesn't look
dead to the end user.

Similarly, I have the same data (with more fields) writing out to a large
HTML table. I've instituted paging on this, at 100 records per page, but it
seems like the first load still takes awhile, I'm guessing to load up the
recordset; it seems to run faster after it gets to the page for the first
time.

I found an article on paging methods earlier in the newsgroup that I'm going
to try for the second issue, but does anyone have any suggestions for the
first issue? I did try a GetRows dump to an array, but that produced no
noticable performance improvement. The procedure does come up quickly in
Query Analyzer as well, so I think it's just the rendering.

Thanks.

--

Kenneth S. McAndrew
Software Developer, Information Concepts
[email]kmcandrew@infoconcepts.com[/email]

[CJM]

I would say that 8000 row in a drop-down list is very excessive.

It can't be of benefit to the user to have to trawl through 8000 lines to
get the value they want. Can you not give them a free-text field to use, and
then validate their input against the data after the event?

Chris

"Ken McAndrew" <kmcandrew@infoconcepts.com> wrote in message
news:u0jBUwwGEHA.1720@tk2msftngp13.phx.gbl...

> I'm trying to work on some performance issues in a couple of areas. The
> scenario is that I have an 8000+ recordset that's returned and intended to
> be written to a drop-down list. This can take up to 10 minutes or more to
> occur. I already have the query in a stored procedure, and I've set up
> disconnected recordsets to try and help as well. With buffering on, there
> doesn't appear to be any action happening, though I'm considering using
> Response.Flush to force a render every 100 records or so, so it doesn't

look

> dead to the end user.
>
> Similarly, I have the same data (with more fields) writing out to a large
> HTML table. I've instituted paging on this, at 100 records per page, but

it

> seems like the first load still takes awhile, I'm guessing to load up the
> recordset; it seems to run faster after it gets to the page for the first
> time.
>
> I found an article on paging methods earlier in the newsgroup that I'm

going

> to try for the second issue, but does anyone have any suggestions for the
> first issue? I did try a GetRows dump to an array, but that produced no
> noticable performance improvement. The procedure does come up quickly in
> Query Analyzer as well, so I think it's just the rendering.
>
> Thanks.
>
> --
>
> Kenneth S. McAndrew
> Software Developer, Information Concepts
> [email]kmcandrew@infoconcepts.com[/email]
>
>
>

[Ken McAndrew]

Unfortunately no, I can't, nor can I limit the dropdown based on some other
entry. It's not every recordset that has this many, and it comes up alright
for 1000-2000 records at times. But there will be some, and I've heard the
possibility of up to 20,000 at a shot. It's only a couple of pages that this
happens on, but they're not in obscure parts of the application.

--

Kenneth S. McAndrew
Software Developer, Information Concepts
[email]kmcandrew@infoconcepts.com[/email]


"CJM" <cjmwork@yahoo.co.uk> wrote in message
news:OBFi10wGEHA.3360@TK2MSFTNGP12.phx.gbl...

> I would say that 8000 row in a drop-down list is very excessive.
>
> It can't be of benefit to the user to have to trawl through 8000 lines to
> get the value they want. Can you not give them a free-text field to use,

and

> then validate their input against the data after the event?
>
> Chris
>
> "Ken McAndrew" <kmcandrew@infoconcepts.com> wrote in message
> news:u0jBUwwGEHA.1720@tk2msftngp13.phx.gbl...

> > I'm trying to work on some performance issues in a couple of areas. The
> > scenario is that I have an 8000+ recordset that's returned and intended

to

> > be written to a drop-down list. This can take up to 10 minutes or more

to

> > occur. I already have the query in a stored procedure, and I've set up
> > disconnected recordsets to try and help as well. With buffering on,

there

> > doesn't appear to be any action happening, though I'm considering using
> > Response.Flush to force a render every 100 records or so, so it doesn't

> look

> > dead to the end user.
> >
> > Similarly, I have the same data (with more fields) writing out to a

large

> > HTML table. I've instituted paging on this, at 100 records per page, but

> it

> > seems like the first load still takes awhile, I'm guessing to load up

the

> > recordset; it seems to run faster after it gets to the page for the

first

> > time.
> >
> > I found an article on paging methods earlier in the newsgroup that I'm

> going

> > to try for the second issue, but does anyone have any suggestions for

the

> > first issue? I did try a GetRows dump to an array, but that produced no
> > noticable performance improvement. The procedure does come up quickly in
> > Query Analyzer as well, so I think it's just the rendering.
> >
> > Thanks.
> >
> > --
> >
> > Kenneth S. McAndrew
> > Software Developer, Information Concepts
> > [email]kmcandrew@infoconcepts.com[/email]
> >
> >
> >

>
>

[dave]

You not only have a recordset problem, the html (size) generated would also
cause the client speed problems. A rethink is in order.


"Ken McAndrew" <kmcandrew@infoconcepts.com> wrote in message
news:OIsmRFxGEHA.3096@TK2MSFTNGP11.phx.gbl...

> Unfortunately no, I can't, nor can I limit the dropdown based on some

other

> entry. It's not every recordset that has this many, and it comes up

alright

> for 1000-2000 records at times. But there will be some, and I've heard the
> possibility of up to 20,000 at a shot. It's only a couple of pages that

this

> happens on, but they're not in obscure parts of the application.
>
> --
>
> Kenneth S. McAndrew
> Software Developer, Information Concepts
> [email]kmcandrew@infoconcepts.com[/email]
>
>
> "CJM" <cjmwork@yahoo.co.uk> wrote in message
> news:OBFi10wGEHA.3360@TK2MSFTNGP12.phx.gbl...

> > I would say that 8000 row in a drop-down list is very excessive.
> >
> > It can't be of benefit to the user to have to trawl through 8000 lines

to

> > get the value they want. Can you not give them a free-text field to use,

> and

> > then validate their input against the data after the event?
> >
> > Chris
> >
> > "Ken McAndrew" <kmcandrew@infoconcepts.com> wrote in message
> > news:u0jBUwwGEHA.1720@tk2msftngp13.phx.gbl...

> > > I'm trying to work on some performance issues in a couple of areas.

The

> > > scenario is that I have an 8000+ recordset that's returned and

intended

> to

> > > be written to a drop-down list. This can take up to 10 minutes or more

> to

> > > occur. I already have the query in a stored procedure, and I've set up
> > > disconnected recordsets to try and help as well. With buffering on,

> there

> > > doesn't appear to be any action happening, though I'm considering

using

> > > Response.Flush to force a render every 100 records or so, so it

doesn't

> > look

> > > dead to the end user.
> > >
> > > Similarly, I have the same data (with more fields) writing out to a

> large

> > > HTML table. I've instituted paging on this, at 100 records per page,

but

> > it

> > > seems like the first load still takes awhile, I'm guessing to load up

> the

> > > recordset; it seems to run faster after it gets to the page for the

> first

> > > time.
> > >
> > > I found an article on paging methods earlier in the newsgroup that I'm

> > going

> > > to try for the second issue, but does anyone have any suggestions for

> the

> > > first issue? I did try a GetRows dump to an array, but that produced

no

> > > noticable performance improvement. The procedure does come up quickly

in

> > > Query Analyzer as well, so I think it's just the rendering.
> > >
> > > Thanks.
> > >
> > > --
> > >
> > > Kenneth S. McAndrew
> > > Software Developer, Information Concepts
> > > [email]kmcandrew@infoconcepts.com[/email]
> > >
> > >
> > >

> >
> >

>
>

[Bob Lehmann]

Well, considering that your select listalone would be around around 350K
<option value="something">Something</option> = 44 bytes * 8000 = 352000
bytes
Why does it surprise you that your page is slow?

Bob Lehmann

"Ken McAndrew" <kmcandrew@infoconcepts.com> wrote in message
news:u0jBUwwGEHA.1720@tk2msftngp13.phx.gbl...

> I'm trying to work on some performance issues in a couple of areas. The
> scenario is that I have an 8000+ recordset that's returned and intended to
> be written to a drop-down list. This can take up to 10 minutes or more to
> occur. I already have the query in a stored procedure, and I've set up
> disconnected recordsets to try and help as well. With buffering on, there
> doesn't appear to be any action happening, though I'm considering using
> Response.Flush to force a render every 100 records or so, so it doesn't

look

> dead to the end user.
>
> Similarly, I have the same data (with more fields) writing out to a large
> HTML table. I've instituted paging on this, at 100 records per page, but

it

> seems like the first load still takes awhile, I'm guessing to load up the
> recordset; it seems to run faster after it gets to the page for the first
> time.
>
> I found an article on paging methods earlier in the newsgroup that I'm

going

> to try for the second issue, but does anyone have any suggestions for the
> first issue? I did try a GetRows dump to an array, but that produced no
> noticable performance improvement. The procedure does come up quickly in
> Query Analyzer as well, so I think it's just the rendering.
>
> Thanks.
>
> --
>
> Kenneth S. McAndrew
> Software Developer, Information Concepts
> [email]kmcandrew@infoconcepts.com[/email]
>
>
>

[Bob Barrows [MVP]]

Ken McAndrew wrote:

> I'm trying to work on some performance issues in a couple of areas.
> The scenario is that I have an 8000+ recordset that's returned and
> intended to be written to a drop-down list. This can take up to 10

You might want to look at my dynamic listbox demo that is available at
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp[/url]

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [MVP]]

> Unfortunately no, I can't, nor can I limit the dropdown

You don't have a choice. The browser is simply going to choke on very large
form elements. No magic trick is going to resolve that. Consider making
them choose the starting letter, or some kind of wildcard search, and going
to the server (in a modal window or hidden frame) to build the dropdown
based on the criteria.