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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

695 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