ASP Speed Tricks

[Shailesh Humbad]

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors might
be interested in the complex table optimizations. Please check it out at:

http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

[Ken Schaefer]

a) Use GetRows/GetString rather than looping through recordsets

b) In the "complex table" scenario, do a JOIN to retrieve all the data as a
single resultset. Combine with .GetRows.

It's not just about speed - you need to look at scalability as well.
Eliminating the need to mulitple recordset objects, and reducing the amount
of time required for connections etc to be held open increases scalability
in terms of the number of users your webapp can support.

Cheers
Ken

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...
: I just posted an article I wrote called ASP Speed Tricks. It covers
: techniques to optimize output of database data in HTML, for
: both simple tables and complex tables. More advanced ASP authors might
: be interested in the complex table optimizations. Please check it out at:
:
: http://www.somacon.com/aspdocs/
:
: Hope you enjoy,
: Shailesh
:
: Please reply at http://www.somacon.com/contact.php
:

[Shailesh Humbad]

Good points. I did not cover GetRows or GetString, as they preclude the
possibility of using VBScript to perform additional formatting to cell
contents. I know that the formatting can be done in the SQL query
itself, but this is usually a little harder and less flexible.

I talked about using Join to retrieve all the 'complex' data as a single
recordset in the section "Methods to Avoid". The rationale for avoiding
this is that the primary table data will be repeated for each joined row
of the secondary table, which in most circumstances would cause excess
overhead. But I can imagine circumstances when this would work out faster.

I am reading up on disconnected recordsets. Otherwise, I don't have
much experience with scalability, as the app I've been working on is for
the Intranet. I should mention this in the article.

Thank you very much for your comments.

Shailesh

Ken Schaefer wrote:

a) Use GetRows/GetString rather than looping through recordsets

b) In the "complex table" scenario, do a JOIN to retrieve all the data as a
single resultset. Combine with .GetRows.

It's not just about speed - you need to look at scalability as well.
Eliminating the need to mulitple recordset objects, and reducing the amount
of time required for connections etc to be held open increases scalability
in terms of the number of users your webapp can support.

Cheers
Ken

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...
: I just posted an article I wrote called ASP Speed Tricks. It covers
: techniques to optimize output of database data in HTML, for
: both simple tables and complex tables. More advanced ASP authors might
: be interested in the complex table optimizations. Please check it out at:
:
: http://www.somacon.com/aspdocs/
:
: Hope you enjoy,
: Shailesh
:
: Please reply at http://www.somacon.com/contact.php
:

[Ken Schaefer]

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:_p******** **************@ twister.columbu s.rr.com...
: Good points. I did not cover GetRows or GetString, as they preclude the
: possibility of using VBScript to perform additional formatting to cell
: contents. I know that the formatting can be done in the SQL query
: itself, but this is usually a little harder and less flexible.
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~

With .GetRows() you can do the formatting as you access the array elements.

..GetString() is faster, but not suitable for situations where flexible
formatting options are required.

~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
: I talked about using Join to retrieve all the 'complex' data as a single
: recordset in the section "Methods to Avoid". The rationale for avoiding
: this is that the primary table data will be repeated for each joined row
: of the secondary table, which in most circumstances would cause excess
: overhead. But I can imagine circumstances when this would work out
faster.
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~

Personally I think the use of multiple recordsets would generally cause more
overhead. I suppose it depends how much info is repeated for each "parent"
record.

Cheers
Ken

[Ken Schaefer]

I agree - for very large recordsets I suspect that time is spent allocating
memory. However I don't think it's practical (or normal) to dump 20,000
records to a webpage :-)

For a webpage that has a lot of reports, you can do something like this:

' Open connection
' Open RS and GetRows (first resultset)
' Open RS and GetRows (second resultset)
' Open RS and GetRows (third resultset)
' Close Connection
'
' Lots of other stuff here
'
'
' Write out first resultset
' Write out second resultset
' Write out third resultset

That way, your connection is only open for a small part of the webpage,
which means (usually) that you can support more clients.

Cheers
Ken
"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:Jz******** **************@ twister.columbu s.rr.com...
: I just found that GetString can be slower than looping through the
: recordset. I modified my example simpletable5.as p to check.
:
: This is the code snippet with GetString:
:
: Response.write objRS.GetString (, , vbTab, vbCrLf, "&nbsp;")
:
: For 20,000 records, the server time was about 2.13 seconds.
:
: This is the current simpletable5.as p algorithm, which produces
: the same output with looping through the Recordset:
:
: Do While Not objRS.EOF
: Response.write objField0
: Response.write vbTab
: Response.write objField1
: Response.write vbTab
: Response.write objField2
: Response.write vbTab
: Response.write objField3
: Response.write vbTab
: Response.write vbCrLf
: objRS.MoveNext
: Loop
:
: For 20,000 records, the server time was about 1.25 seconds.
:
: This is rather surprising to me! I think it is because GetString needs
: to do memory allocation to create the output string, whereas
: Response.write just dumps out the data as soon as it is generated.
:
: Shailesh
:
: Ken Schaefer wrote:
:
: > a) Use GetRows/GetString rather than looping through recordsets
: >
: > b) In the "complex table" scenario, do a JOIN to retrieve all the data
as a
: > single resultset. Combine with .GetRows.
: >
: > It's not just about speed - you need to look at scalability as well.
: > Eliminating the need to mulitple recordset objects, and reducing the
amount
: > of time required for connections etc to be held open increases
scalability
: > in terms of the number of users your webapp can support.
: >
: > Cheers
: > Ken
: >
: > "Shailesh Humbad" <hu******@hotma il.com> wrote in message
: > news:IQ******** **************@ twister.columbu s.rr.com...
: > : I just posted an article I wrote called ASP Speed Tricks. It covers
: > : techniques to optimize output of database data in HTML, for
: > : both simple tables and complex tables. More advanced ASP authors
might
: > : be interested in the complex table optimizations. Please check it out
at:
: > :
: > : http://www.somacon.com/aspdocs/
: > :
: > : Hope you enjoy,
: > : Shailesh
: > :
: > : Please reply at http://www.somacon.com/contact.php
: > :
: >
: >
:

[Shailesh Humbad]

It would be nice to one day be able to dump 20000 records to a webpage.
The long list would definitely be useful for something like an embedded
search box, as in http://www.somacon.com/phpref/ . Anyway, GetString is
only slower for the larger recordsets, as you pointed out. It is only
somewhat faster than the optimized looping for the smaller recordsets.
Here are the test results for the other cases:

Records GetString Time Looping Time KBytes
1000 0.08 0.10 25
2000 0.13 0.17 52
10000 0.50 0.67 262
20000 2.13 1.25 536

Time is seconds to run the entire page, and is the approximate average
of 5-10 test runs. KBytes is the size of the output of the page.

Shailesh

Shailesh Humbad wrote:

I just found that GetString can be slower than looping through the
recordset. I modified my example simpletable5.as p to check.

This is the code snippet with GetString:

Response.write objRS.GetString (, , vbTab, vbCrLf, "&nbsp;")

For 20,000 records, the server time was about 2.13 seconds.

This is the current simpletable5.as p algorithm, which produces
the same output with looping through the Recordset:

Do While Not objRS.EOF
Response.write objField0
Response.write vbTab
Response.write objField1
Response.write vbTab
Response.write objField2
Response.write vbTab
Response.write objField3
Response.write vbTab
Response.write vbCrLf
objRS.MoveNext
Loop

For 20,000 records, the server time was about 1.25 seconds.

This is rather surprising to me! I think it is because GetString needs
to do memory allocation to create the output string, whereas
Response.write just dumps out the data as soon as it is generated.

Shailesh

Ken Schaefer wrote:

a) Use GetRows/GetString rather than looping through recordsets

b) In the "complex table" scenario, do a JOIN to retrieve all the data
as a
single resultset. Combine with .GetRows.

It's not just about speed - you need to look at scalability as well.
Eliminating the need to mulitple recordset objects, and reducing the
amount
of time required for connections etc to be held open increases
scalability
in terms of the number of users your webapp can support.

Cheers
Ken

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...
: I just posted an article I wrote called ASP Speed Tricks. It covers
: techniques to optimize output of database data in HTML, for
: both simple tables and complex tables. More advanced ASP authors might
: be interested in the complex table optimizations. Please check it
out at:
:
: http://www.somacon.com/aspdocs/
:
: Hope you enjoy,
: Shailesh
:
: Please reply at http://www.somacon.com/contact.php
:

[Egbert Nierop \(MVP for IIS\)]

hailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors might
be interested in the complex table optimizations. Please check it out at:

http://www.somacon.com/aspdocs/
Another thing bad for scalability AND performance is this

' Connect to the data source
objCN.Connectio nString = "DSN=datasource "
objCN.Open

You should advise to use OLEDB instead of ODBC.
success

--
compatible web farm Session replacement for Asp and Asp.Net
http://www.nieropwebconsult.nl/asp_session_manager.htm

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

[Egbert Nierop \(MVP for IIS\)]

"Bob Barrows" <re*******@yaho o.com> wrote in message
news:uf******** ******@TK2MSFTN GP12.phx.gbl...

Shailesh Humbad wrote:

Set rs = server.createob ject("adodb.rec ordset")
objCN.Procedure OrQueryName param1,..,param M, rs And I would recommand not to use SErver.Createob ject but CreateObjecet
avoiding another extra code-layer that is not necesarry on W2k and higher.
(It was however on NT4)...
;-) Bob Barrows

[Bob Barrows]

Egbert Nierop (MVP for IIS) wrote:

"Bob Barrows" <re*******@yaho o.com> wrote in message
news:uf******** ******@TK2MSFTN GP12.phx.gbl...

Shailesh Humbad wrote:

Set rs = server.createob ject("adodb.rec ordset")
objCN.Procedure OrQueryName param1,..,param M, rs

And I would recommand not to use SErver.Createob ject but CreateObjecet
avoiding another extra code-layer that is not necesarry on W2k and
higher. (It was however on NT4)...
;-)

Oops, I forgot :-)
I'm still on NT4 - it will be easier to remember when we finally move to
IIS5, I hope :-)

Thx,
Bob

[Shailesh Humbad]

In my Intranet medical office application, many of the pages allow the
user to select a patient, physician, or insurance, and these lists can
easily have up to thousands of entries. The asynchronous server call
has too high latency for common, repeated use. On the other hand, I
agree that scroll bars and drop down lists are awful user interface
elements. I have written an extensive article on this, and have come up
with what I think is a better solution. It implements the Winamp "Jump
To File" feature in Javascript. If you're interested, read about it here:

http://somacon.com/jumpto/userinterface.php

And try it out and get the source code here:

http://www.somacon.com/phpref/

This interface requires that all the options are pre-loaded in the page
that is sent to the browser. Since people are moving to higher
bandwidth connections all the time, and the Intranet is already fast
enough, this is more and more becoming acceptable in practice.

Shailesh
Chris Barber wrote:

There are better ways to do this that don't entail sending 20,000 records to
the client browser. Some form of asynchronous call to the server to process
the search term and return a list of associated entries. After all, who is
every going to scroll down a list that has 20,000 entries. I get exasperated
just scrolling through about 50-100.

Chris.

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:pf******** **************@ twister.columbu s.rr.com...

It would be nice to one day be able to dump 20000 records to a webpage.
The long list would definitely be useful for something like an embedded
search box, as in http://www.somacon.com/phpref/ . Anyway, GetString is
only slower for the larger recordsets, as you pointed out. It is only
somewhat faster than the optimized looping for the smaller recordsets.
Here are the test results for the other cases:

Records GetString Time Looping Time KBytes
1000 0.08 0.10 25
2000 0.13 0.17 52
10000 0.50 0.67 262
20000 2.13 1.25 536

Time is seconds to run the entire page, and is the approximate average
of 5-10 test runs. KBytes is the size of the output of the page.

Shailesh

Shailesh Humbad wrote:

I just found that GetString can be slower than looping through the
recordset. I modified my example simpletable5.as p to check.

This is the code snippet with GetString:

Response.wri te objRS.GetString (, , vbTab, vbCrLf, "&nbsp;")

For 20,000 records, the server time was about 2.13 seconds.

This is the current simpletable5.as p algorithm, which produces
the same output with looping through the Recordset:

Do While Not objRS.EOF
Response.write objField0
Response.write vbTab
Response.write objField1
Response.write vbTab
Response.write objField2
Response.write vbTab
Response.write objField3
Response.write vbTab
Response.write vbCrLf
objRS.MoveNext
Loop

For 20,000 records, the server time was about 1.25 seconds.

This is rather surprising to me! I think it is because GetString needs
to do memory allocation to create the output string, whereas
Response.wri te just dumps out the data as soon as it is generated.

Shailesh

Ken Schaefer wrote:
a) Use GetRows/GetString rather than looping through recordsets

b) In the "complex table" scenario, do a JOIN to retrieve all the data
as a
single resultset. Combine with .GetRows.

It's not just about speed - you need to look at scalability as well.
Eliminati ng the need to mulitple recordset objects, and reducing the
amount
of time required for connections etc to be held open increases
scalabili ty
in terms of the number of users your webapp can support.

Cheers
Ken

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ**** *************** ***@twister.col umbus.rr.com...
: I just posted an article I wrote called ASP Speed Tricks. It covers
: techniques to optimize output of database data in HTML, for
: both simple tables and complex tables. More advanced ASP authors
might
: be interested in the complex table optimizations. Please check it
out at:
:
: http://www.somacon.com/aspdocs/
:
: Hope you enjoy,
: Shailesh
:
: Please reply at http://www.somacon.com/contact.php
: