Posted on 1998-10-18
Last Modified: 2013-12-25
I would like to know PROVEN TECHNICAL WAYS to speed up my ASP Pages.
  I am loading data from 10 Tables from a MS-SQL server .
I am using a recordset for each table. All these recordsets use only 1 single page-scope connection. I am using ODBC driver.
Question by:john_19
  • 2
  • 2
  • 2
  • +2
LVL 28

Expert Comment

ID: 1855791
Your problem might be in SQL language, not in ASP.

It is very inefficient to use a recordset for every table if you need data from 10 tables. It is possible that you need only one recordset in total.

What you could do is:
1. make a view in MS SQL that fits your needs, and your that view to create a recordset.
2. Make a SQL statement using many JOINs. If you have trouble writing JOIns, use the Query wizard in Access: using drag & drop, you can easily create a complicated SQL statement.


If you mean that it just takes too long to write so much data to a single file, then the problem might be that you use a table to display the data. Tables give the browser a lot of calculations to do, and that counts especially when the tables are long and complicated.


Maybe you mean something else

LVL 10

Expert Comment

ID: 1855792
Remove as much from the ASP file as you can... ie comments, "debugging" references, and white space... Like sybe said, though, it doesn't lie in the ASP, but rather what is ran from the ASP page... Also, think of upgrading your server... and make sure you have the newest versions of the ASP software and ADO stuff.

Expert Comment

ID: 1855793
With all due respect, if you're interested in speed, you shouldn't be using NT.  Sorry, but the difference in performance and stability between SQL Server/ASP and Unix-based systems using shtml is exponential.  I don't want to start a flame-war with people who feel a need to defend their Microsoft investment, but it is true.  If you have better things to do than reboot your server 4-5 times a day, consider porting your app to a Unix-based system.

LVL 28

Expert Comment

ID: 1855794
Make sure the webserver and the MS SQL server are on different machines
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.


Expert Comment

ID: 1855795

I have been unhappy with the speed of asp accessing sql files... but, you should test to see if the problem is in ASP or SQL/ODBC calls?

We have ran into problems with ASP. (Interdev 6 installed caused this)
Also, we have noticed some increase with adding memory to the server.
  (moving from 96mb to 128mb)

For us, we noticed that using the 'native' Oracle code was about 15% faster than using the ODBC code to access Oracle tables.

good luck!


Accepted Solution

tjp earned 50 total points
ID: 1855796
Some of this I got from a Microsoft article called "Improve the performance of you MDAC application" and some is from my own experiences as an ASP developer and IIS/SQL server admin.

1.  Use the SQLOLEDB driver, not the generic ODBC driver.  Do this by changing your connect string to include "Provider=SQLOLEDB"

2.  Use stored procs wherever possible.  They are generally much faster than using inline SQL in an ASP.  If the procs don't return any rows, be sure to use the adCommandNoExecute option to stop your code from creating a recordset that it will never even use.

3.  If you have a highly accessed site, make sure that you enable connection pooling.

4.  Disassociate those recordsets from the database and free up the connection immediately.  Do this by setting the recordset's ActiveConnection to Nothing, and then close the connection and set it to Nothing as well.

Finally, consider creating a DLL in either C++ or VB to run on the server and simply instantiate this object on your ASPs.  This allows to do several other performance enhancers that simply aren't possible in a normal ASP, especially being able to strongly type your variables.  (i.e. don't need to use Server.CreateObject() anymore, which is slow)

C++ is best, because there are ADO extensions which eliminate the overhead of passing Variants around too.


Expert Comment

ID: 1855797
As far as that comment about UNIX vs. NT goes...rubbish.  If your NT admin can't keep it from crashing 4-5 times a day (I'd be embarrassed if my servers crashed 4-5 times a year), he/she needs to find a different line of work.  NT is every bit as scalable as UNIX, especially now that there's finally a clustering solution available for IIS 4.0 (called Convoy).  I'll take NT 4 Enterprise Ed./IIS 4.0/SQL Server running on an multiprocessor Alpha system over any UNIX-based solution you can dream up.  The problem is with Intel processors and their associated chipsets, not with NT itself.  Can't wait til NT 5 is fully available to eliminate the problem with thunking on 64-bit processors...

To the person using Oracle through ODBC:  there's OLE DB drivers available for Oracle as well.  I'm not sure what the exact provider name is, but it does exist...

Expert Comment

ID: 1855798
To  tjp...   NT crashing is the norm when you are pushing the software.  Specifcally, with the memory holes in ASP and Interdev 6.    Also mind that Interdev 6's editor is worse than frontpage98 and the interdev/webbots administration is worse at best.  We even tracked down some access (acl) problems starting with the install of interdev 6.

The alpha vrs intel issue, is pretty moot, since, we have seen alpha 2100/4100 have as many weird 'quirks' as the intel boxes.
Different quirks, but, still bummers.

Currently, working on issue with the content indexer / search engine failing on a 2100. (with no error message).

Hey, no problem.  Job security.   Its a consultant's wetdream.  


Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now