Online Transaction Processing vs. Decision Support

Online Transaction Processing

Online transaction processing database applications are optimal for managing changing data. These applications typically have many users who are performing transactions at the same time that change real-time data. Although individual requests by users for data usually reference few records, many of these requests are being made at the same time. Common examples of these types of databases are airline ticketing systems and banking transaction systems. The primary concerns in this kind of application are concurrency and atomicity.

Concurrency controls in a database system guarantee that two users cannot change the same data, or that one user cannot change a piece of data before another user is finished with it. For example, if you are talking to an airline ticket agent to reserve the last available seat on a flight and the agent starts the process of reserving the seat in your name, another agent should not be able to tell another passenger that the seat is available.

Atomicity guarantees that all the steps in a transaction are completed successfully as a group. If any step fails, no other steps should be completed. For example, a banking transaction may involve two steps: taking funds out of your checking account and putting them into your savings account. If the step that removes the funds from your checking account succeeds, you want to make sure that the funds are put into your savings account or put back into your checking account.

Online Transaction Processing Design Considerations

Transaction processing system databases should be designed to promote the following:

• Good data placement

  I/O bottlenecks are a big concern for OLTP systems, because of the number of users modifying data all over the database. When you design a database, determine the likely access patterns of the data and combine frequently accessed data together. Use filegroups and RAID (redundant array of independent disks) systems to help in this.

• Short transactions to minimize long-term locks and improve concurrency

  Avoid user interaction during transactions. Whenever possible, run a single stored procedure to process the whole transaction. The order in which you reference tables within your transactions can affect concurrency. Put references to frequently accessed tables at the end of the transaction to minimize the duration that locks are held.

• Online backup

  OLTP systems are frequently characterized by continuous operations in which down time is kept to an absolute minimum. That is, they operate 24 hours a day, 7 days a week. Although the SQL Server Database Engine can back up a database while it is being used, schedule the backup process to occur during times of low activity to minimize the effects on users.

• High normalization of the database

  Reduce redundant information to increase the speed of updates and improve concurrency. Reducing data also improves the speed of backups, because less data has to be backed up.

• Minimal or no historical or aggregated data

  Data that is rarely referenced can be archived into separate databases, or moved out of the heavily updated tables into tables that contain only historical data. This keeps tables as small as possible, and improves backup times and query performance.

• Careful use of indexes

  Indexes must be updated every time a row is added or modified. To avoid over-indexing heavily updated tables, keep indexes narrow. Use the Database Engine Tuning Advisor to design your indexes.

• Optimum hardware configuration to handle the large numbers of concurrent users and quick response times required by an OLTP system

Decision Support

Decision-support database applications are optimal for data queries that do not change data. For example, a company can periodically summarize its sales data by date, sales region, or product, and store this information in a separate database to be used for analysis by senior management. To make business decisions, users must be able to determine trends in sales quickly by querying the data based on various criteria. However, they do not have to change this data. The tables in a decision-support database are heavily indexed, and the raw data is frequently preprocessed and organized to support the various types of queries to be used. Because the users are not changing data, concurrency and atomicity issues are not a concern; the data is changed only by periodic, bulk updates made during off-hour, low-traffic times in the database.

Decision Support Design Considerations

Decision-support system databases should be designed to promote the following:

• Heavy indexing

  Decision-support systems have low update requirements, but large volumes of data. Use many indexes to improve query performance.

• Denormalization of the database

  Introduce pre-aggregated or summarized data to satisfy common query requirements and improve query response times.

• Use of a star or snowflake schema to organize the data within the database.