[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now



Posted on 1998-10-18
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 28

Expert Comment

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

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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

650 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