Whether it is an Internet or Intranet, a web catalog or an e-commerce site with shopping cart, somewhere these applications are accessing a data source.
The essence of the database driven websites or applications is the data access methodology adopted. The type of data access or the way in which the data is accessed can make a difference between a good and a bad web site, a consistent user experience and the performance. This article discusses various implementations of data access methodologies and their pros and cons.
Data access is a necessary and inseparable part of database driven applications irrespective of which database being used. The type of data access can also be different depending on what business application it is being used for like online catalogs, transaction oriented sites, employee information in corporate intranets or just information lookup websites. The key component, however, is that some form of data access has to be provided.
Microsoft’s ActiveX Data Objects (ADO), is a popular choice of data access today, which can be used to access the data in multiple formats, from relational databases to simple text files, via OLE DB. ADO was originally created for use for Internet applications running on Microsoft’s IIS web server and used from Active Server Pages (ASP) pages.
The server-side scripts in ASP pages can directly access databases using ADO. An instance of the objects in the ADO object model is created in an ASP page to open a connection to database, fetch a recordset or add/modify data in a table. For example, to open a connection to a database which has a system DSN created for it named as “testDSN”, an instance of Connection object would be created like this :
-==-
Or to fetch some data from this database, you would use a Recordset object like this :
-==-
This technique of data access works for the most part. But if the database structure is changed – for example, if a column is added or removed from a table, or a field data type is changed – then, these changes are to be made to each page wherever the changed data item is accessed.
On the plus side, opening a connection to data source on every page just before it is required and closing immediately after that in the ASP page, is a good performance benefit. Releasing the database connection quickly allows database connection pooling. This way, the connection is optimally used, as it is active for a short duration when it is needed. For maintenance sake, if you do not want to have connection information on every page, you can create a database access ASP page with connection information and then include that file in every ASP page where you intend to use that connection.
Another technique used for opening a connection is, assigning the Connection object to an application -level object. Though this allows for easy maintenance as any changes to the connection string are to be applied at only one place, it has serious performance issues. This technique maintains an open connection to the database throughout the life of the application. That means, system has to maintain an open connection to the database even if it is not being used.
Connection object should not be stored even as a session-level object, which keeps the connection to the database open for a complete user session, irrespective of whether it is being used in that session or not. Thus, even if a user is filling out some information on a form or just browsing the site, the connection is kept open till it takes the user to respond to the page. One workaround is, store the connection string as a session-level object, which can then be used to create any new data source connection and also can be easily maintained. As this string can contain data source location, userid and password information needed to access the data, the user gets the same type of connection resulting in a consistent data interface.
Also, one thing to note is, when you create an instance of an ADO object and store it in an application or session level object, the object gets locked down to a specific thread. This is because, though ADO library objects are created as both-threaded objects, but they are registered as apartment-threaded objects. Hence, storing ADO objects in application or session objects can limit the overall performance of the application.
Another way of implementation is encapsulating it in an ActiveX component, usually a .dll file. The ActiveX components used from ASP pages are often called ASP components. When the ADO objects are wrapped in an ASP component, this ASP data access component becomes an extra layer between ASP pages and the database. So, instead of having several ASP pages making the same direct database access, an ASP data access component can be created and used to perform this recurring operation and then instantiated in every ASP page needing database access. With this approach, the changes to underlying data structure, results in changes to just one component rather than all pages that use that particular changed data item, thus increasing maintainability. As components are reusable entities, the same component can be used many times without having to recode the data access each and every time it is needed.
The components are pre-compiled, ready -to-execute entities. So, unlike ASP pages, which need to be parsed line by line at web server, the component does not, thus enhancing the performance. The encapsulation of connection information in a component also provides for added security as the information is shielded in a dll. The passing of sensitive information as userid, password, and database location in a string from an ASP page can be a target of malicious intent. But the same is shielded when it is done between the component and OLE DB provider. The use of DSN in an ASP file can also be a preferred method instead of using connection string in an ASP page.
This implementation also has some downsides to it. As the components are pre-compiled entities, their modification is not as easy as changing the connection string in an ASP file. You have to recode it in Visual Basic or VC++ and re-compile and re-register on the web server. Then there are debugging issues. If the error is in the component, may be a logical error or syntax error or business rule error, then the developer has to go through complete cycle of recoding, recompiling and re-registering again and again till the problem is solved.
The use of components allows you to create a library of data access components catering to various needs of data access in your application. You can encapsulate complex queries, stored procedure call along with any parameters in the component and attain considerable performance improvement.
Thus, the choice of implementation of data access really varies based on the requirements of the application being developed. If the application does not need frequent modification to the database structure or the database and the connection information is not going to change frequently, then encapsulating that info in an ASP component may be a good idea. Also, if application includes, complex database operations, business rules, which are not going to change frequently then ASP components, can provide significant performance benefits. Otherwise, instead of using ASP components for data access, the connection information can be used from a DSN on web server in an include ASP file and this file can be included in all ASP files needing data access. The stored procedures can be used for recurring or complex queries, or even for encapsulating business rules.
The issues discussed in this article can serve to avoid some common pitfalls and achieve some performance benefits in practice. This article has tried to put pros and cons of encapsulating data access logic in a component, as well as other implementations in practice. Hopefully, this information will prove useful. Good Luck!
Atul Totre is the Director of Web and e-Business at Telezoo corp (http://www.telezoo.com) – a B2B telecom facilitated marketplace. He is the Chief web architect for telezoo and has earlier worked as Software Consultant for GlaxoSmithKline, Roche and AT&T. Atul is very active in various user groups in Washington metropolitan area and has published many articles sharing his knowledge and experience with fellow software developers.
