In your ASP code, are you looping through data extracted from SQL Server? Or are you manipulating large datasets within your ASP code? If so, these can hurt performance. Instead, consider performing most, if not all, data manipulation from within stored procedures on SQL Server. This will not only speed data manipulation, it will also reduce network bandwidth between your web server and SQL Server, boosting overall performance. [6.5, 7.0, 2000] Added 6-27-2002
*****
For maximum performance, consolidate most, if not all, of your COM components in a single DLL. COM components instantiated from a single DLL instantiate faster than if they are called from multiple DLLs. [6.5, 7.0, 2000] Added 10-26-2000
*****
If you need to display a lot of data from SQL Server on a web page, consider enabling page buffering for the web server (buffering is off by default in IIS 4, but is on by default in IIS 5). You can also enable page buffering via ASP code, using “response.buffer=true” on each page that you want to enable page buffering on. Enabling page buffering can significantly speed up pages with lots of data. If you do enable page buffering, the entire page will not be displayed until it is complete. If your page takes a while to create, and you don’t want to make your users look at a blank screen, you can use “response.flush” as needed to break up the page into two or more parts that are displayed on the screen as they are completed. [6.5, 7.0, 2000] Added 10-26-2000
*****
Because users are fickle, you can’t depend on them waiting for a web page to be processed and displayed on their screen. If the time it takes between when they submit a page request and for the final page to be displayed is very long, they may get tired of waiting and move onto another page. If this happens, then you have wasted a lot of processing for nothing. If you have complex pages that take some time to create, you may want to consider using the “response.isclientconnected” method after running long queries, but before building complex pages. This way, if the visitor has left your web page, you don’t have to bother with building the complex page, saving your server some overhead. To abort the page, all you have to do is run “response.end.” [6.5, 7.0, 2000] Updated 4-3-2006
*****
If you use collections in your VB 6 or ASP code, consider instead using dictionaries in order to boost the performance of your application. While you are probably familiar with what and how collections are used, you may not be familiar with dictionaries. The dictionary class (the Scripting.Dictionary Object) is part of the Microsoft Scripting Runtime library, which is delivered with the VB 6 and ASP development environments.
Like collections, dictionaries can hold any type of data, and items can be retrieved by using a key, or iterated using the “For Each” syntax. But dictionaries are different in that offer properties and methods not available for collections. And the biggest difference is that they are much faster, about twice as fast. If you haven’t learned about dictionaries yet, you need to take the time now to learn about their numerous advantages. [6.5, 7.0, 2000] Added 12-27-2000
*****
(Part 1) Many ASP developers create their own search engines that return back the results to the web browser one page at a time, and allowing the user to continue paging through the results until they have found what they are looking for. How this paging is implemented can significantly affect the performance of your website.
One common way to handle this paging is to use client-side cursors. Coding is easy and this method works fine. But the problem with client-side cursors is that all the results of the search, not just each single page, must be transferred from SQL Server to the web server (assuming your SQL Server and web server are on separate boxes), which is acting as the client. If the total search results include 1,000 rows, that is a lot of data to move from the SQL Server to the web server to be managed by a client-side cursor. And if users are running hundreds, if not thousands of searches each day, this can cause a large performance hit on the web server.
Another option is to use a server-side cursor instead. While this will probably give better performance than a client-side cursor in the above example, service-side cursors aren’t always the best solution when performance is an important issue. As discussed in other areas of this website, cursors of any type are best avoided.
A more efficient way to handle paging is to use SQL Server to perform most of the work, and only send each page of data that is requested by the user to the web server, not all potential pages in one shot. This method can be implemented using a SQL Server stored procedure, a SQL Server temp table to store the results of the user’s query, and a sessions table to track each user.
For example, when a user first connects to your website, you can manage the user’s session state by storing it in a SQL Server table, and the results of each user’s query is inserted into a separate temporary table that has an Identify column. The Identify column is used to determine how to divide up the result set into pages. Then one page worth of rows (you determine the number of rows) at a time is returned to the web server. As part of your ASP code, you will have to send the stored procedure the necessary parameters so that it knows which page you want to display each time, along with the necessary state information. Once the session is ended, the temp table can be deleted.
An additional way to enhance the performance of using a stored procedure and temp table is to include the TOP operator as part of your SELECT statement in the stored procedure. The TOP operator allows you to limit how many rows are to be returned. For example, if it is possible to return thousands of rows, but you feel it is unlikely that most users will not want to page more than, say 10 pages of 20 rows each, then you might want to limit the number of rows to perhaps 200 rows. You will have to select the ideal number of rows to use in the TOP operator based on how you think your search engine will be used. If you are bound and determined to use client-side cursors, you might want to consider using the TOP operator in your SELECT statement to help reduce the number of rows sent to the client (web server), instead of sending every potential row.
[6.5, 7.0, 2000] Tip submitted by Alban Schmid. Added 1-22-2001
*****
(Part 2) Instead of creating a temporary table with an identity column to create a numbered column, as described in the above tip, you can instead write the query using a correlated subquery. (When I say numbered column, I mean when you need a column in your recordset that is a number from 1 to n, n being the number of records returned from your query.)
So our task at hand is to write a query that adds a numbered column to our recordset, and only returns a predetermined number of records for the given page. Here is the query:
DECLARE @PAGESIZE INT
DECLARE @CURRENT_PAGE INT
SET @PAGESIZE = 5
SET @CURRENT_PAGE = 2
SET ROWCOUNT @PAGESIZE
SELECT AU_ID, AU_LNAME, AU_FNAME, PHONE,
(SELECT COUNT(*) FROM AUTHORS A2 WHERE A2.AU_LNAME <= A.AU_LNAME AND AU_FNAME LIKE ‘%A%’) AS RowNumber
FROM AUTHORS A
WHERE AU_FNAME LIKE ‘%A%’ AND (SELECT COUNT(*) FROM AUTHORS A2 WHERE A2.AU_LNAME <= A.AU_LNAME AND AU_FNAME LIKE ‘%A%’) > (@PAGESIZE * @CURRENT_PAGE) – @PAGESIZE
ORDER BY AU_LNAME
SET ROWCOUNT 0
I declared the variables so you could easily turn this into a stored procedure. Also note that you can remove the correlated subquery from the list of columns to be selected. Even though it shouldn’t hurt the query’s performance, you shouldn’t return a column unless you are going to use it.
[6.5, 7.0, 2000] Contributed by Justin Gunther. Updated 3-8-2001