Hello List,



I'm writing simply to share a little learning experience I had today.



Today I was continuing work on a project that involves several repeated

calls to a pretty heavy dataset (4100 records at the moment).  For this

particular page I am using it on, I have to create a select box displaying

this list of data, and it can occur several times. In one instance it

repeats some 12 times. YIKES! That's a lot of rows.  Now, before you stop

and ask, "Why don't you create a text string with all those options once,

then just call that?!" I will explain why I can't.  Each time the select box

is created is for a different row in the main loop.  The proper option for

that selector must be set at selected.  So short of using another

alternative (which I'll mention later), I have to go through the record each

time to catch the right option.



Initially, I set up the query at the beginning of the page, create the

recordset for it, then run the recordset loop once for each selector created

in the main loop.  With only 4 of these selectors, the page was taking 6+

seconds to load.  Keep in mind this is running on my local machine (DB is on

LAN).



So I start looking into the loop timing, and I find that each selector loop

is taking about 800 milliseconds to complete.  After a bit of thought, I do

a surprisingly quick recode to take the initial recordset after the query

and dump it to an array using GetRows().  After I redid the selector loop to

use the array instead of the recordset, I re-ran the page and reduced the

loop times to around 120 milliseconds each!  I didn't need a separate

recordset for each selector now (there were 2 plus another from the master

loop recordset - also in an array) so I changed it so all the queries used

the same RS, then I destroyed it after all the query and GetRow calls, but

before any of the display code.



Having removed the extra objects and using the arrays instead of the

Recordset objects, I reduced the load time about 90%!  This was using very

basic timing methods, but needless to say, there's a bit of an improvement

there. :)



Since joining this list I have seen countless threads asking "How do I get

the count of rows from my query results?".  Yet another major advantage I

found is the convenience of the Ubound of the Getrows() array to supply the

row count! Very handy.



So the last thought I had about a solution to my repeating selectors was

this:

Create the option list (which is always the same aside from the selected

item) in a string which I can call for each selector.  Then, create a

dynamic JavaScript snippet at the end of the page that sets the selected

index of each selector to the appropriate option based on the item in the

main loop.  This is only slightly tricky because I have to either "search"

the option array for the "item" which is the option's value (somewhat

laborious), or I have to figure out the option's index value (faster but

much trickier).  Either way, some kind of loop is involved.



So all-in-all, the use of Getrows made things run much faster and seems like

a pretty good method when dealing repetitively with large recordsets.  Hope

someone can make some use of this information, and feel free to email if you

are curious to learn more.



Happy Programming!



Peter

Hi Peter,



While reading this, one more thing came to mind.



Let's over simply stuff a little and assume your OPTION value has a simple 

ID. Now, assume your pull-down string for all records looks like this:



<option value=""1"">1</option><option value=""2"">2</option><option 

value=""3"">3</option>



etc etc. At the top of your page, you create this string once.



Now for every time you need to pre-select an item, how about this code 

right before you write it to the browser:



Dim sID

sID = "1"

sMyOptionList = Replace ("value=""" & sID & """", "value=""" & sID & """ 

selected""")



If you quote the value-attribute, you can always be sure that there will 

only be one value="n", so you can be certain that the replace will function 

correctly.



How does that sound?? It makes it more down-level browser compatible 

because you don't need JavaScript.



I haven't checked the actual code for quotes etc, but I am sure you catch 

my drift.



Regards,



Imar





At 04:18 PM 4/17/2001 -0400, you wrote:

>Hello List,

>

>I'm writing simply to share a little learning experience I had today.

>

>Today I was continuing work on a project that involves several repeated

>calls to a pretty heavy dataset (4100 records at the moment).  For this

>particular page I am using it on, I have to create a select box displaying

>this list of data, and it can occur several times. In one instance it

>repeats some 12 times. YIKES! That's a lot of rows.  Now, before you stop

>and ask, "Why don't you create a text string with all those options once,

>then just call that?!" I will explain why I can't.  Each time the select box

>is created is for a different row in the main loop.  The proper option for

>that selector must be set at selected.  So short of using another

>alternative (which I'll mention later), I have to go through the record each

>time to catch the right option.

>

>Initially, I set up the query at the beginning of the page, create the

>recordset for it, then run the recordset loop once for each selector created

>in the main loop.  With only 4 of these selectors, the page was taking 6+

>seconds to load.  Keep in mind this is running on my local machine (DB is on

>LAN).

>

>So I start looking into the loop timing, and I find that each selector loop

>is taking about 800 milliseconds to complete.  After a bit of thought, I do

>a surprisingly quick recode to take the initial recordset after the query

>and dump it to an array using GetRows().  After I redid the selector loop to

>use the array instead of the recordset, I re-ran the page and reduced the

>loop times to around 120 milliseconds each!  I didn't need a separate

>recordset for each selector now (there were 2 plus another from the master

>loop recordset - also in an array) so I changed it so all the queries used

>the same RS, then I destroyed it after all the query and GetRow calls, but

>before any of the display code.

>

>Having removed the extra objects and using the arrays instead of the

>Recordset objects, I reduced the load time about 90%!  This was using very

>basic timing methods, but needless to say, there's a bit of an improvement

>there. :)

>

>Since joining this list I have seen countless threads asking "How do I get

>the count of rows from my query results?".  Yet another major advantage I

>found is the convenience of the Ubound of the Getrows() array to supply the

>row count! Very handy.

>

>So the last thought I had about a solution to my repeating selectors was

>this:

>Create the option list (which is always the same aside from the selected

>item) in a string which I can call for each selector.  Then, create a

>dynamic JavaScript snippet at the end of the page that sets the selected

>index of each selector to the appropriate option based on the item in the

>main loop.  This is only slightly tricky because I have to either "search"

>the option array for the "item" which is the option's value (somewhat

>laborious), or I have to figure out the option's index value (faster but

>much trickier).  Either way, some kind of loop is involved.

>

>So all-in-all, the use of Getrows made things run much faster and seems like

>a pretty good method when dealing repetitively with large recordsets.  Hope

>someone can make some use of this information, and feel free to email if you

>are curious to learn more.

>

>Happy Programming!

>

>Peter

    Ha.  Nice.  I'm about to attack this same issue tonight in fact.  I have

a kabillion select lists that are based on what the user has selected and

again on records from the database.  Needless to say the correct options

must be selected each time and the page must requery every time they change

to another page of the main tab set.



    One way to return your recordcount is to open the recordset with

adOpenStatic.  By default all recordsets are opened as adOpenForwardOnly

which means once you go forward the last record is dropped from memory;

adOpenForwardOnly records can't return the count because if they jump all

the way to the end for a count, what's the point in opening it in the first

place (so they just return 0).



    rsData.Open "SELECT * FROM [user]", conConnection, adOpenStatic +

adCmdText



    Of course you must provide the ADODB constants for this to work properly

(which is something I have a question on in a seperate e-mail).



- Chuck



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

From: "Peter Lanoie" <planoie@e...>

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

Sent: Tuesday, April 17, 2001 4:18 PM

Subject: [asp_databases] Why Recordset.Getrows is great.





> Hello List,

>

> I'm writing simply to share a little learning experience I had today.

>

> Today I was continuing work on a project that involves several repeated

> calls to a pretty heavy dataset (4100 records at the moment).  For this

> particular page I am using it on, I have to create a select box displaying

> this list of data, and it can occur several times. In one instance it

> repeats some 12 times. YIKES! That's a lot of rows.  Now, before you stop

> and ask, "Why don't you create a text string with all those options once,

> then just call that?!" I will explain why I can't.  Each time the select

box

> is created is for a different row in the main loop.  The proper option for

> that selector must be set at selected.  So short of using another

> alternative (which I'll mention later), I have to go through the record

each

> time to catch the right option.

>

> Initially, I set up the query at the beginning of the page, create the

> recordset for it, then run the recordset loop once for each selector

created

> in the main loop.  With only 4 of these selectors, the page was taking 6+

> seconds to load.  Keep in mind this is running on my local machine (DB is

on

> LAN).

>

> So I start looking into the loop timing, and I find that each selector

loop

> is taking about 800 milliseconds to complete.  After a bit of thought, I

do

> a surprisingly quick recode to take the initial recordset after the query

> and dump it to an array using GetRows().  After I redid the selector loop

to

> use the array instead of the recordset, I re-ran the page and reduced the

> loop times to around 120 milliseconds each!  I didn't need a separate

> recordset for each selector now (there were 2 plus another from the master

> loop recordset - also in an array) so I changed it so all the queries used

> the same RS, then I destroyed it after all the query and GetRow calls, but

> before any of the display code.

>

> Having removed the extra objects and using the arrays instead of the

> Recordset objects, I reduced the load time about 90%!  This was using very

> basic timing methods, but needless to say, there's a bit of an improvement

> there. :)

>

> Since joining this list I have seen countless threads asking "How do I get

> the count of rows from my query results?".  Yet another major advantage I

> found is the convenience of the Ubound of the Getrows() array to supply

the

> row count! Very handy.

>

> So the last thought I had about a solution to my repeating selectors was

> this:

> Create the option list (which is always the same aside from the selected

> item) in a string which I can call for each selector.  Then, create a

> dynamic JavaScript snippet at the end of the page that sets the selected

> index of each selector to the appropriate option based on the item in the

> main loop.  This is only slightly tricky because I have to either "search"

> the option array for the "item" which is the option's value (somewhat

> laborious), or I have to figure out the option's index value (faster but

> much trickier).  Either way, some kind of loop is involved.

>

> So all-in-all, the use of Getrows made things run much faster and seems

like

> a pretty good method when dealing repetitively with large recordsets.

Hope

> someone can make some use of this information, and feel free to email if

you

> are curious to learn more.

>

> Happy Programming!

>

> Peter

>

>

> ---

> SoftArtisans helps developers build robust, scalable Web applications!

> Excel Web reports, charts: http://www.softartisans.com/excelwriter.html

> File uploads: http://www.softartisans.com/saf.html

> Transactional file management: http://www.softartisans.com/saf1.html

> Scalability: http://www.softartisans.com/saxsession.html

> ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html




$subst('Email.Unsub')

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

:     One way to return your recordcount is to open the recordset with

: adOpenStatic.  By default all recordsets are opened as adOpenForwardOnly

: which means once you go forward the last record is dropped from memory;

: adOpenForwardOnly records can't return the count because if they jump all

: the way to the end for a count, what's the point in opening it in the

first

: place (so they just return 0).

:

:     rsData.Open "SELECT * FROM [user]", conConnection, adOpenStatic +

: adCmdText



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



No, no no



Please read:

www.adOpenStatic.com/faq/recordcountalternatives.asp



The suggestion of using .getRows to populate VBScript arrays and use

UBound() to get the Recordcount is an excellent suggestion - much

faster/better than using expensive cursors like adOpenStatic.



Cheers

Ken

I knew SOMEone would think of something. Doh!

That's a good one.  Never even crossed my mind.... I suspect that should

work quite well.



You are most correct sir, the value will always be unique thus I could do a

replace on it in the quoted attribute.  Then before the next find, I just

have to find " selected" and clear it out to reset that option.  Only one

loop needed to create the options.  And to better that, I can put the text

for the option tag right into my SQL query, then use Getstring with blank

delimiters!  HAH!



SELECT '<option value="'+RTRIM(CompanyID)+'">'+CompanyName+'</option>' FROM

companymaster ORDER BY CompanyName



I just implemented this solution and cut my time in half. Now I am down to

about 60ms from 120ms for each main loop using your suggestion. Not bad for

about 4100 records!



Marvelous!  I tip my hat to you Imar, once again.



Thanks!



Peter



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

From: Imar Spaanjaars [mailto:Imar@S...]

Sent: Tuesday, April 17, 2001 5:24 PM

To: ASP Databases

Subject: [asp_databases] Re: Why Recordset.Getrows is great.





Hi Peter,



While reading this, one more thing came to mind.



Let's over simply stuff a little and assume your OPTION value has a simple

ID. Now, assume your pull-down string for all records looks like this:



<option value=""1"">1</option><option value=""2"">2</option><option

value=""3"">3</option>



etc etc. At the top of your page, you create this string once.



Now for every time you need to pre-select an item, how about this code

right before you write it to the browser:



Dim sID

sID = "1"

sMyOptionList = Replace ("value=""" & sID & """", "value=""" & sID & """

selected""")



If you quote the value-attribute, you can always be sure that there will

only be one value="n", so you can be certain that the replace will function

correctly.



How does that sound?? It makes it more down-level browser compatible

because you don't need JavaScript.



I haven't checked the actual code for quotes etc, but I am sure you catch

my drift.



Regards,



Imar





At 04:18 PM 4/17/2001 -0400, you wrote:

>Hello List,

>

>I'm writing simply to share a little learning experience I had today.

>

>Today I was continuing work on a project that involves several repeated

>calls to a pretty heavy dataset (4100 records at the moment).  For this

>particular page I am using it on, I have to create a select box displaying

>this list of data, and it can occur several times. In one instance it

>repeats some 12 times. YIKES! That's a lot of rows.  Now, before you stop

>and ask, "Why don't you create a text string with all those options once,

>then just call that?!" I will explain why I can't.  Each time the select

box

>is created is for a different row in the main loop.  The proper option for

>that selector must be set at selected.  So short of using another

>alternative (which I'll mention later), I have to go through the record

each

>time to catch the right option.

>

>Initially, I set up the query at the beginning of the page, create the

>recordset for it, then run the recordset loop once for each selector

created

>in the main loop.  With only 4 of these selectors, the page was taking 6+

>seconds to load.  Keep in mind this is running on my local machine (DB is

on

>LAN).

>

>So I start looking into the loop timing, and I find that each selector loop

>is taking about 800 milliseconds to complete.  After a bit of thought, I do

>a surprisingly quick recode to take the initial recordset after the query

>and dump it to an array using GetRows().  After I redid the selector loop

to

>use the array instead of the recordset, I re-ran the page and reduced the

>loop times to around 120 milliseconds each!  I didn't need a separate

>recordset for each selector now (there were 2 plus another from the master

>loop recordset - also in an array) so I changed it so all the queries used

>the same RS, then I destroyed it after all the query and GetRow calls, but

>before any of the display code.

>

>Having removed the extra objects and using the arrays instead of the

>Recordset objects, I reduced the load time about 90%!  This was using very

>basic timing methods, but needless to say, there's a bit of an improvement

>there. :)

>

>Since joining this list I have seen countless threads asking "How do I get

>the count of rows from my query results?".  Yet another major advantage I

>found is the convenience of the Ubound of the Getrows() array to supply the

>row count! Very handy.

>

>So the last thought I had about a solution to my repeating selectors was

>this:

>Create the option list (which is always the same aside from the selected

>item) in a string which I can call for each selector.  Then, create a

>dynamic JavaScript snippet at the end of the page that sets the selected

>index of each selector to the appropriate option based on the item in the

>main loop.  This is only slightly tricky because I have to either "search"

>the option array for the "item" which is the option's value (somewhat

>laborious), or I have to figure out the option's index value (faster but

>much trickier).  Either way, some kind of loop is involved.

>

>So all-in-all, the use of Getrows made things run much faster and seems

like

>a pretty good method when dealing repetitively with large recordsets.  Hope

>someone can make some use of this information, and feel free to email if

you

>are curious to learn more.

>

>Happy Programming!

>

>Peter