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.
Who is Participating?
tjpConnect With a Mentor Commented:
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.

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

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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.

Make sure the webserver and the MS SQL server are on different machines

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!

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...
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.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.