Database Performance In the grand scheme of things, database actions are slow- very slow. Your ASP application could run at much faster speeds if it didn't need to interact with a database. The performance hit associated with using databases, though, is well worth it due to the number of benefits one receives from tying in a database to a web application. So, since we are already taking a hit when connecting to and querying a database, we want to make sure that we are taking the smallest possible performance hit. There are a number of factors that can affect database performance: How you connect to a database What type of recordset cursor/locktype you use How often you reference the database variables So, let's start from the top and work our way down! Maximizing Performance when Connecting to a Database: The best way to connect to a database is to use OLEDB. There is a great article already on 4Guys that explains what OLEDB is, and how to use it. Please take the time to read that article. If you have a choice of using a System DSN or DSN-less connection, be sure to use a DSN-less connection, which has been shown to demonstrate better performance with many concurrent connections. What type of recordset cursor/locktype: When you explicitly create a recordset object, you can set its cursor and locktype. A recordset's cursor determines how it handles updates to the dataset it's currently working on, while the locktype determines how the recordset will perform updates. Vast performance differences can be seen with large queries when using different types of cursors and locktypes. There is a thorough article on the performance issues with cursors & locktypes. There is also a benchmark report on the results of the various cursors and locktype combinations. How often you reference the database variables: Each time you read an ADO variable, you are expending unneeded clock cycles. Say that you are retreiving a name from a database row and want to display it in several places on the webpage. Rather than calling Response.Write "Hello, " & objRS("Name") & "." Response.Write "It's good to see you " & objRS("Name") & "!" you'll see a performance boost if you store the database result in a local variable, and then using that variable to display the name in various places on the page: Dim strName strName = objRS("Name") Response.Write "Hello, " & strName Response.Write "It's good to see you " & strName & "!" There is a great article describing this topic in further detail. By striving to maximize your database connection and querying performance, your web application will glide along that much faster. No need to take a performance hit when you don't need to... Happy Programming!

Article Information
Article Title:	Database Performance
Article Author:	Scott Mitchell
Article URL:	http://www.4GuysFromRolla.com/webtech/faq/Databases/faq7.shtml