OLTP

OLTP SYSTEM

Databases must often allow the real-time processing of SQL transactions to support real time time-critical applications. This type of processing is known as online transaction processing (OLTP). Enterprise-level relational database management software, such as SQL Server, was designed originally to centrally store the data generated by the daily transactions of large companies or government organizations. Over the decades, these databases have grown to be highly efficient systems for recording the data required to perform the daily operations of the enterprise. Because the system is based on computers and records the business transactions of the enterprise, these systems are known as online transaction processing (OLTP) systems.

The data in OLTP systems is organized primarily to support transactions, such as:

• Recording an order from a point-of-sale(store) terminal or entered through a Web site.
• Placing an order for more supplies when inventory levels drop to a defined level.
• Recording employee data.

Individual transactions are completed quickly and access relatively small amounts of data. OLTP systems are designed and tuned to process hundreds or thousands of transactions being entered at the same time.Although OLTP systems excel at recording the data required to support daily operations,OLTP data is not organized in a manner that easily provides the information required by managers to plan the work of their organizations. Managers need summary information from which they can analyze trends that affect their organization or team. They need to find the critical factors affecting the success of their organization, and how best to adjust those factors to improve the success of the enterprise. They need to find how the workload of their enterprise is affected by seasonal and yearlytrends so that they can predict how many employees and resources will be required to perform future work.

OLTP systems can handle:

• Manual transaction capturing
• Batch transaction processing of current data
• Transactional information
• Current business activities
• · Current workflow including stage and state
• · Search and find functionality of recent business transactional activity
• · Document creation for the purpose of carrying out a transaction, e.g. Invoices, Delivery notes,
• Job cards.
• Historical detailed transactions
• Time sensitive information

OLTP systems can’t handle:

• Reporting
• Management information
• Decision support systems
• Aggregated and summarized information

Advantages:

• It provides faster and more accurate forecast for revenues and expenses.

• It provides a concrete foundation for a stable organization because of timely modification of all transactions.
  

• It makes the transactions much easier on behalf of the customers by allowing them to make the payments according to their choice.
  

• It broadens the customer base for an organization by simplifying and speeding up

individual processes.

• Small current footprint of data: The OLTP database should be optimized with a small footprint of information that allows the business to continue trading today. This will allow DBA'sto manage backups more efficiently where only current data is backed up. Historicaltransactions could be scheduled over quite times.
  

• Normalized:The database focuses on highly normalized tables with strong database referential backing. This will assist in the drive to ensure the current data is accurate and notcompromised.
  

• Performance tuned:The tables will have a minimal set of indexes on it as to support fast transactional processing
  

• Historical transaction splitting: In very large databases, historical data is normally partitionedinto separate tables, thus allowing for smaller current tables with current and active data. In the event that historical transactions need to be queried, it can be used as the front-end to access the history table.

• Data locks: As the active data set will be smaller, lock durations may bedecreased.

• Lock contention: As the OLTP solution will be in a separate database compared to OLAP data,long running reports will not affect the current transactional processing tasks by holding “readlocks” on database for a long period of time.

• Deadlock reduction: Although deadlocks are created by inconsistent data access, many OLTP versus OLAP deadlocks could be avoided. Deadlocks are caused by Insert, Update and Delete actions conflicting with other Insert, Update, Delete and Select statements on the same database.

Disadvantages:
Online Transaction Processing (OLTP) has the following disadvantages:
• The data is not properly organized.
• The data is highly volatile.
• The OLTP system does not maintain history.
• Data is in highly normalized form
• This data is maintained at different platforms with different data structures