DB "Connections" That Don’t Drain Performance

Connecting to a database improperly can be one of the greatest drains on system resources. Learn how "Connection Objects" can help architect reusable DB connects without killing performance.

Tags: Connection, Database, Connection Object, Minor Changes, Calling Script, System Resources, Significant Decrease,



Easy integration with a database is one of the hallmarks of a successful business Internet site. Isn't it ironic that connecting to the database improperly is one of the greatest drains on system resources? This is particularly true if you connect to the database multiple times on the same page.



Fortunately, this 'resource drain' can be reduced with some minor changes in how you connect to the database. These changes will allow you to reuse connections rather than creating them on demand.



The first thing that you should do is to put the code that connects to the database in a separate file or subroutine rather than everywhere else it is used. This has two advantages. First, you only have to type the code once. Second, when the database password changes, you will have to change only a single page rather than searching through the entire application.



This function declares and creates a Connection object. Then, it populates that object with the proper values and opens it for use. Finally, the function returns the object to the calling script. Although this function is set up to connect to an Oracle database, it would only require minor changes to connect to a different type of database. While the code below is from an Active Server Page Include file, a similar connection function could be written for Visual Basic or Java.

Function getDBConnection()
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "MSDAORA"
conn.Open "orcl","scott","tiger"
Set getDBConnection = conn
End Function
As mentioned above, this is a standard connection function and is quite common. The problem is that every time this function is called, it creates a new connection to the database adding overhead and slowing the script.



Here is that same routine rewritten to allow multiple uses of a single connection. Again, this is written in ASP although the technique can be used in any language.


Dim conn
Dim connLoaded

connLoaded = False

Function getDBConnection()
If ( connLoaded = False ) Then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider = "MSDAORA"
conn.Open "orcl","scott","tiger"
connLoaded = True
End If
Set getDBConnection = conn
End Function
In this function, the Connection object is declared outside of the function itself. Essentially, this makes it and the other variable, connLoaded, global variables which can then be accessed from anywhere in the application.



When this function is called, the Connection object is not automatically opened. Instead, the IF statement checks to see if there is already one loaded. If there is not one loaded, then it will create a new one and return it to the calling script. However, if a Connection object has already been opened, it will return that object to the calling script.



Making this minor change to the code can cause a significant savings in system resources. Exactly how much will depend on to how often you connect to the database. If you only connect once per page, then you will not see any difference whatsoever. If you connect two to three times per page, then you could see a small decrease in the time it takes the page to load (less than 5%). However, if you connect seven to eight times per page, then you will notice a significant decrease in the loading time (up to 10%).



Al Hetzel is an Oracle/Web Developer in Dallas, Texas. You can reach him at alhetzel@yahoo.com.





back