Performance Tuning ASP Pages Using SQL Server

Don’t cache COM objects (database or of any type for that matter) in either Session or Application objects. If you do, COM objects cached in Session objects will tie the Session to an ASP worker thread, preventing it from being used elsewhere. If a COM object is cached in an Application object, all calls to the object have to be marshaled and serialized, adding substantial overhead to your application. In other words, don’t cache COM objects in Session or Application objects. [6.5, 7.0, 2000]

*****

If database access via your web server is maxing out your SQL Server, and you have done everything you can to optimize the SQL Server’s performance, consider replicating the databases hit by your web server onto multiple SQL Servers. Then rewrite the ASP code or COM objects to take turns accessing each separate physical SQL Server in a round-robin approach.

To replicate the data between your SQL Servers, you can use SQL Server’s built-in replication feature, or you can periodically manually copy the database from one SQL Server to another. What method you select will depend on if the databases are read-only, or include transactions.

If you need to replicate transactions, then you will need to use SQL Server’s Merge Replication feature. If you only need to replicate read-only data, you can use SQL Server’s Transactional Replication, Snapshot replication, or manually copy the databases between servers on a regular basis. E-commerce applications that require constant access to the same database server may not be able to take advantage of this. [6.5, 7.0, 2000]

*****

If a single SQL Server is not big enough to handle the load of your website, consider using two or more SQL Servers, separating transactional data from read-only data. For example, all the transactional data from an e-commerce application could be sent to one SQL Server, and all requests for building web pages that show-off products could come from a separate SQL Server with read-only data. [6.5, 7.0, 2000] Added 6-27-2002

*****

If your web server, and its backend SQL Server database server, are not overwhelmed with work, consider locating both of them on the same physical server. This will eliminate the overhead of sending network communications between separate web and SQL Servers, providing an incremental boost to performance.

But if your web server and SQL Server running together max out a single server, then separate them. Although the network overhead comes back to haunt you when you do this, running them on two servers makes your application much more scalable for busy applications. [6.5, 7.0, 2000]

*****

If the database your ASP pages are hitting is read-only, consider setting the database option to “read only”. This will turn off locking in the database, giving a small performance boost to your queries. If you do make your database “read-only, be sure to update the database’s statistics first.

If you have only one database that handles both reads and writes, consider segregating the data into two databases. Use one database for read-only data and use the other database for writing data. This will boost performance because locking is eliminated in the “read-only” database and reduced in the “write” database. [6.5, 7.0, 2000]

*****

If you have retrieved data from SQL Server and need to reuse the data over and over in your ASP page, assign the data to local variables. If you try to retrieve the data from a collection over and over, you will be creating unnecessary overhead. Local variables will always be faster to access than a collection. [6.5, 7.0, 2000]

*****

Perform simple data validation at the browser instead of at the SQL Server. This helps to reduce traffic between the browser and the server, and reduces the load on both the web server and the SQL Server. [6.5, 7.0, 2000, 2005]

*****

Instead of using session variables to store state information, use SQL Server tables instead. While session variables are very convenient for the developer, they carry with them extra overhead and aren’t very scalable. While there are a variety of ways to avoid session variables, one technique you can use, which is faster and much more scalable, is to store the state information in a SQL Server row, and then retrieve it later when needed. Many large web sites use this method for their shopping cards. At first glance, it may appear that creating a database connection for this purpose may create too much overhead, but this is not true if database connection pooling is properly implemented. [6.5, 7.0, 2000]

*****

Don’t use transactions when accessing SQL Server if you don’t need them. Not all requests to SQL Server, such as generic SELECT statements used to download read-only data to a browser, don’t need to be encapsulated inside a transaction. While transactions serve an important purpose, they do incur overhead, and should only be used when they are really needed. So don’t include BEGIN TRANSACTION and COMMIT TRANSACTION statements in your Transact-SQL code unless needed. [6.5, 7.0, 2000] Updated 12-14-2000

*****

Don’t use ASP-based Component Services transactions if you don’t need them. ASP transaction processing is based on the Component Services transaction environment, which is used to update databases, such as SQL Server, reliably. This is invoked through ASP code by including <%@ TRANSACTION=Required %> in your ASP pages. If you need to force two or more operations to perform as a single unit, then use this feature. But if you do not, avoid using it, as it adds overhead to your ASP page. [6.5, 7.0, 2000] Added 12-14-2000

*****

If you are using MTS (Microsoft Transaction Server) components to access SQL Server, don’t forget to call the SetComplete or SetAbort methods as appropriate. This helps to ensure that server resources are released as soon as possible, freeing up overhead and boosting scalability and performance. [6.5, 7.0, 2000]

*****

When creating a Connection object, always create it explicitly, not implicitly. Opening a Connection object explicitly consumes less resources than opening it implicitly, and it also allows you to more efficiently manage multiple connections and to reassign the various roles that the Connections objects perform within your application. [6.5, 7.0, 2000] Added 12-14-2000

*****

When using recordsets, be sure to open them explicitly, not implicitly. When recordsets are opened implicitly, you cannot control the default cursor and lock types, which are, respectively, forward-only and read-only. If you always open your recordsets explicitly, then you can specify which cursor and lock types you want to invoke for this particular situation, specifying the types with the least amount of overhead to accomplish the task at hand. [6.5, 7.0, 2000] Added 12-14-2000

*****

Be sure you explicitly close any recordsets or object variables you create to access SQL Server, and as soon as they are no longer required. This helps to free up resources as soon as possible and reducing overhead. In addition, close them as soon as you’re done with them, not at the end of the ASP page. This helps to reduce database connection overhead. [6.5, 7.0, 2000]

*****

When making your connection to SQL Server, choose DSN-less connections for the fastest connection. If you do need to use a DSN, select System DSNs over File DSNs, because they are faster when making connections. [6.5, 7.0, 2000]

*****

If you have a choice between using ODBC or OLE DB to access your SQL Server database, choose OLE DB as it is generally faster. [7.0, 2000]

*****

When using ADO to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of “connection creep”. Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server’s performance. [6.5, 7.0, 2000]

Continues…

Leave a comment

Your email address will not be published.