January 2012. Volume 1 - Issue 1
By Tom Romeo

The goal of this article is to help developers design applications with consideration for the importance of proper database interaction for maximum performance.

I kid you not; I have worked on a project with experienced developers to produce an ASP.NET Web application that is effectively single user. Let's try that again, single user web application, ludicrous you might be thinking, but the trap is very easy to fall into when strictly adhering to the merits of OOP, {object oriented programming}.

After installing the software and creating a new empty database, a single user can experience deadlocks while entering initial data into the system. How can one user invoke database calls that cause deadlocks?

The automated test scripts can only run when no one else is on the site. A single user can crash the system if attempting to add data while the automated test scripts are running.

How can this happen? It happens when most of the rules of database transaction processing are ignored.

There is no way to eliminate the possibility of a deadlock, but there are ways to avoid them and to handle them when they do occur.

If you search Google or Bing, you will find scores of articles on the proper way to structure database transactions or how to avoid deadlocks or OLTP performance, so this should not be new material, however I will attempt to convey it as simply as possible.

Here is a list of the DO's which are easily found on the net.

• Make your transactions as short as possible.
  • This means only include things into a transaction that must all occur on success or not occur at all if any piece fails
  • It also means to only manipulate the rows that belong to the transaction which should be few. Large SQL batches affecting hundreds or thousands of rows should be performed by means other than SQL transactions.
• Try to begin and commit your transaction in as few SQL batches as possible, preferably one.
  • This means a transaction should be performed from begin through commit in one round trip to the database server.
• Gather all of the data necessary for the transaction prior to beginning the transaction, and then perform the whole transaction in one batch.
• Make sure that all of the queries within each transaction are as efficient as possible.
  • This applies to query structure as well as database definition.
  • Make sure the appropriate primary keys and indexes are present
  • Avoid unnecessary indexes
  • In a later article well explore queries that adversely affect CPU, memory, disk, or all three
• If your database server has an option for implicit transactions, be sure to turn this off.
  • If implicit transactions are used then unnecessary locks will occur for simple select queries
• Choose the proper database transaction isolation level, ie. Are dirty reads ok?
• Use stored procedures when possible, understanding that some systems must use some amount dynamic SQL per their purpose or design.
• Always introduce the tables used in the transaction in the same order to reduce the possibility of deadlocks.
• Access the least amount of data possible while in the transaction.
• Test your transactions on a database that has enough data to resemble real world conditions.
• Test your transactions in a multi user environment.

Here is a list of the DON'T's

• If you are manipulating thousands of records in multiple tables, DO NOT wrap this in a SQL transaction, not only will it lock up the system for the duration, in SQL server tempdb and the transaction log will become very busy hogging up disk which in turn will slow the entire server down, similarly in Oracle the rollback segments will be effected in the same way. Find a way to do it in smaller batches or find other ways to guarantee the data is manipulated correctly without using a SQL transaction.
• DO NOT issue a begin transaction in a single batch and proceed to perform multiple additional SQL batches followed by a commit at the end. This will impose unnecessarily long locks causing others to wait for database resources. For example DO NOT DO THIS:
  • Open DB Connection
  • Execute "Begin Transaction"
  • Select * from tblFooBar"
  • Execute "Update tblFoo"
  • Execute "Update tblBar"
  • Execute "Delete tblFooAgain where ..."
  • Execute "Commit Transaction"
  • Close DB Connection
    • Each execution is a round trip to the database server. It is far better to send everything including and between the Open DB Connection and Close DB Connection in one round trip to the database server.
• DO NOT issue a "select * " statement on any table from within a transaction. If a select must be performed from within a transaction select only the columns necessary and use the appropriate where conditions to return only the rows that are needed.
• DO NOT use database objects that you do not understand, they may introduce unwanted conditions. Always learn as much about the tool you are using before using it. Sometimes the tool that you choose to make the coding job easier adversely impacts trouble shooting, and performance.
• DO NOT test your application with just a few records in a relatively empty database or as a single user.

It might seem obvious to not issue a transaction in multiple database server round trips, but if your developers used rigid OOP design methodology then they probably built a bunch of objects each of which is related in some way to a table or tables in the database. In this fashion they may execute a SQL "Begin transaction" and then instantiate several objects each accessing their part of the database. This of course issues locks on some level for each of the tables associated with the objects. Then they perform any inserts, updates and deletes before issuing a "commit transaction". In this way the OOP design was paramount and the database impact was secondary or incidental. In an effort to fix the problem, many times the band-aid approach is used by including database query hints. This is not recommended by Oracle or Microsoft. Hints are available to be used in special circumstances.

It is however not so obvious even to experienced developers to limit the size of the transactions. Many developers believe that they can issue a "Begin transaction" followed by as much SQL as they like and that the ending "Commit Transaction" will guarantee that the entire transaction will either succeed or fail with a "Rollback Transaction". In SQL Server this will tax your transaction log and tempdb. In Oracle it will tax your rollback segments. All of this translates to disk utilization of > 100% and massive disk queuing. When that happens, all database activity slows down giving the illusion that the database server is the bottleneck, when in fact it is the badly constructed transaction.

Always remember that your system can only perform as fast as its slowest component. Many times the biggest bottleneck of an application that uses a SQL database in the back end is disk. In our next article we'll cover how to find your system's bottle necks and tackle them one by one.

I am not just regurgitating information I've found on the WEB in attempt to boost my hit ratio. I have found this information in the past and have employed it to make significant performance gains in other systems. If you are experiencing similar issues, you can use these techniques, but first you need to learn how to diagnose the issues to discover the root cause of your problems.

In a windows and SQL Server environment, perfmon, debugging tools for windows, SQL Trace, SQL Tuning Advisor, PSSDIAG, and SQL Nexus can be your best friends.

Oracle is significantly more complex in that it is a multi-platform product, however if your Oracle server is running on a Windows Server, then perfmon, and debugging tools for windows will still help, as will understanding how to use the Oracle system tables, and the Oracle console applications.

Here are some other sources of the same information.

MSDN - Coding Efficient Transactions

Tips for Performance Tuning SQL Server OLTP Databases

Click here to see other articles in the series.