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!
