While ADO makes database manipulation easy for the ASP page developer, using ADO methods to access SQL Server data can often kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in stored procedures instead of using ADO methods. This bypasses object library overhead, reduces the chatter between the VB application and SQL Server over the network, and lets you take advantage of the pre-compilation and reuse stored procedures offer. [6.5, 7.0, 2000] Updated 2-11-2005
*****
When creating a Command object to execute a stored procedure against SQL Server, you can use either the adCmdText or the adCmdStoredProc property to tell ADO that you want to execute a stored procedure. Always use the adCmdStoredProc property, which uses RPCs between the client and SQL Server. This acts to bypass parameter translation and boosts performance from 20 to 30 percent over using the adCmdText property. [6.5, 7.0, 2000] Updated 2-11-2005
*****
If you need to execute a stored procedure from a Command object, and if the stored procedure will not return any rows, you can boost performance of the Command object by setting the adExecuteNoRecords option. This tells the Command object to not ask for a returning rowset, which saves a little overhead and reduces memory usage. [6.5, 7.0, 2000] Updated 2-11-2005
*****
When retrieving data from a SQL Server database, take full advantage of views when appropriate. This is especially true if you are not encapsulating your Transact-SQL in stored procedures as recommended. While calling a view is not usually as efficient as using a stored procedure to retrieve data, it is more efficient that using dynamic Transact-SQL in your ASP code or COM components. [6.5, 7.0, 2000]
*****
If possible in your application, use stored procedures to “batch” a set of related Transact-SQL statements together, instead of calling a separate stored procedure for every database task you want to perform. This helps to reduce network traffic and server overhead. [6.5, 7.0, 2000] Updated 2-11-2005
*****
When calling SQL Server stored procedures from the ADO Command object, don’t use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic and slows performance. Instead, explicitly create the parameters yourself using ADO code. [7.0, 2000] Updated 2-11-2005
*****
When building a web page based on data retrieved from a SQL Server database, only return the exact amount of data you need, no more. Returning data you won’t use when building your web page wastes resources and hurts performance. [6.5, 7.0, 2000] Added 6-27-2002
*****
Don’t store your web page images in a database using SQL Server’s image data type, it is much too slow. The image data type should generally be avoided because of its poor performance. Instead, store images on the web server in a folder, and then store the URL in the SQL Server database. Whenever you need to dynamically create a page with images, your code can retrieve the URLs of the images and then insert the URLs into the page as it is dynamically created. When the page is displayed in a visitor’s web browser, the images are retrieved directly from your web server.
This way, you have the advantage of managing image information in a database, but you don’t have the overhead of storing the actual image in the database. [6.5, 7.0, 2000, 2005]
*****
ADO allows you to create four different types of SQL Server cursors. Each has its own place, and you will want to choose the cursor that uses the least possible resources for the task at hand. When at all possible, attempt to use the Forward-Only cursor, which provides the least amount of overhead of the four cursor types. [6.5, 7.0, 2000]
*****
Implement database connection pooling to reduce the number of connections there are between IIS and SQL Server. Connection pooling allows a single database connection to be shared by multiple users at the same time, reducing SQL Server overhead and increasing scalability and performance. Connection pooling can be implemented through ODBC and MTS (Microsoft Transaction Server). [6.5, 7.0, 2000]
*****
If your ASP pages are connecting to SQL Server via either OLE DB (version 2.0 or higher) or ODBC (version 3.0 or higher), SQL Server connection pooling is automatically implemented for you. Because of this, you don’t have to write special code to implement connection pooling yourself. In addition, you don’t want to even reuse an ADO connection object, which is commonly done by many VB developers.
If you want to take the best advantage of database connection pooling, and optimize your VB application’s SQL Server data access, the best advice you can receive is to be sure that you only open a database connection just before you need it, and then close it immediately after you are done with it. Don’t leave database connections open if you are not using them.
When you create or tear down a database connection in your code, you aren’t really creating a new connection or tearing down a current connection. What is happening is that your connection requests are send to OLE DB or ODBC, and they determine if a connection needs to be created or torn down. If a new connection is needed, then one is created, or one is used from the current connection pool. And if you request that a connection be torn down, it will actually pool the unused connection until it is needed, or tear it down if it is not reused within a given time period. [6.5, 7.0, 2000] Updated 4-3-2006
*****
In order for connection pooling to work correctly, be sure each connection you open uses the same ConnectionString parameters. Connection pooling only works if all of the parameters for the ConnectionString are identical. If they are all not identical, then a new connection will be opened, circumventing connection pooling. [6.5, 7.0, 2000] Updated 4-3-2006
*****
Avoid using the TEXT or NTEXT data types in your ASP applications. Both of these data types can slow down data retrieval substantially. Instead, try to use CHAR, NCHAR, VARCHAR, or NVARCHAR data types instead. If the data you need to retrieve is greater than 8,000 characters, consider splitting your data between two or more columns, retrieving the data as a unit, and the recombining it on a web page.
Another option, if the text is just too long, is to store the a TEXT or NTEXT data type, but then to write it out as static HTML file onto the web server whenever the text is changed, not every time the ASP page is called. This will substantially reduce the overhead due to the use of the TEXT or NTEXT data type. Then have your database store the URL or file location to the static text page, and then insert the file into your ASP pages when the page is called by a user. If you like, use #INCLUDE FILE to insert the static HTML page into your ASP page. [6.5, 7.0, 2000]
*****
If your ASP pages reuse the same static data over and over again, don’t automatically just store this data in your database and retrieve it every time it is needed. Instead, cache it on the web server itself. It is much faster for IIS to get the data from the web server than from the database each time it is needed. Of course this doesn’t apply to dynamic data. But many web sites have some relatively static data that is kept in a database that is better stored and served from a web server instead.
A real world example of this is a list of all 50 states that needs to appear in a drop-down box on a web page form. Other examples of the types of data that you might want to cache include: DHTML scraps, XML strings, menu items, configuration variables, DSNs, IP addresses, and web paths.
There are a number of ways to cache commonly used data. Some of them include using the LookupTable object, or to store the data in an Application variable. [6.5, 7.0, 2000]
*****
Don’t cache an ADO database connection in the Application object for use on multiple web pages. If you do, then all the pages will fight for the use of this connection. In addition, don’t cache an ADO connection in the Session object. If you do, a database connection will be created for every user, defeating connection pooling and overusing server resources. Instead, ADO connections should be created and destroyed on every ASP pages that needs to use ADO. This permits connection pooling and ensures all connections are efficiently created and destroyed. [6.5, 7.0, 2000]
*****
If your ASP pages perform a lot of database access, consider putting the database access code in compiled COM objects instead of in ASP code on individual pages. The COM objects can be loaded on the web server (probably using MTS, but not necessarily), and they can then be called from your ASP pages. Because COM objects are compiled, they often can speed up database access and improve scalability.
Making the decision whether to use code in an ASP page or in a COM object is not always so clear. In many cases, using COM objects to access SQL Server is faster than using ASP code, but in other cases ASP code can be faster than using COM components. This is especially true if you are using IIS 5.0 under Windows 2000.
With COM objects, keep in mind that instantiating a COM object also takes time. If your data access code is small, and is not reused in your application, then including it in an ASP page may be more efficient that putting it in a COM object.
So what should you do, use COM objects or ASP code to access SQL Server? The only way you will know for sure, for your particular situation, is to test, trying both methods to see which option provides the best performance for you. [6.5, 7.0, 2000] Updated 12-21-2000