Database Use or Database Abuse?!

Have you seen these bug reports in your application?

Search results not loading quickly:
When the user searches on a large data set, the results page takes very long to load.

Page time out error:
When multiple users concurrently access the application, the web page times out

Updating a record takes too long:
Time taken to update a record is unacceptably slow when more than one user is accessing the application

Application hangs when multiple users log in:
The application responds well in a single user environment but when more than one user logs in simultaneously, the application fails to respond.

If any of these are problems noticed in the product, you are likely encountering a bottleneck in the data layer of the application. Some simple techniques while coding can avoid such problems after the product is released to the market.

Potential root causes of the above problems are

• Poor database design
• Inadequately configured hardware infrastructure
• Programming errors such as
  • Poor use of Database Connections
  • Ignoring database indexes
  • Using unoptimized SQL code

Here are some tips to detect ‘Programming errors’ during the development stage:

1. Profile application performance: Using AOP (aspect oriented programming), profile the application methods and assess where the time is getting consumed. If the database connection consumes a lot of time, then we can dig further to localize the problem.
   
   
2. Analyse SQL statements: Using analysis tools, the time taken to execute the SQL statements needs to be collected. Tools like IronEye SQL from http://www.IronGrid.com intercept the interaction between data layer component and the database and measure the time taken. This would help narrow down the SQL statements that are culprits in delaying application response.
   
   
3. Check if the code uses Indexes properly: This is a typical problem where the code does *not* leverage the indexes available. The result is a much slower fetch call which could result in page time out errors if the data volume is very large.
   
   
4. Review code for open database connections: After the completion of a database transaction, the application layer code needs to release the database connection back to the connection pool. If this is not coded for, the connections get used up and the application fails to respond under load.
   
   

Closing remarks:

Identifying bottlenecks in data layer and resolving them can be accomplished through the use of appropriate tools and techniques. Early detection and resolution directly accelerates product release schedules and the techniques presented above are a few common ones among many techniques available.