Decision-Support Applications

Decision-support applications provide information for strategic planning, decision making, and report preparation. Decision-support applications frequently generate queries that require the database server to scan entire tables and manipulate large amounts of data. These queries can require operations such as multiple joins, temporary tables, and hundreds, if not thousands, of calculations. For example, the following queries should use the PDQ features of the database server:

• Based on the predicted number of housing starts, the known age of existing houses, and the observed roofing choices for houses in different areas and price ranges, what roofing materials should we order for each of our regional distribution centers?
• How does the cost of health-care plan X compare with the cost of health-care plan Y, considering the demographic profile of our company? Would plan X be better for some regions and plan Y for others?

Such operations require large amounts of data and large amounts of memory. As a result, the execution times for decision-support applications are far longer than the execution times required for typical OLTP applications. Other typical decision-support applications include payroll, inventory reporting, and end-of-period accounting reports. These applications are frequently executed in a batch environment.

Queries that contain one or more of the following operations require large quantities of memory:

• Hash joins
• Sorting
• Groups

Other factors can also influence how the database server allocates resources to a query. Consider the following SELECT statement:

SELECT col1, col2 FROM table1 ORDER BY col1

If no indexes exist on table1, a sort is required, and hence the database server must allocate memory and temporary disk space to sort the query. However, if column col1 is indexed, the query does not require these resources.

Decision-support applications have the following characteristics:

• Complex queries that involve large amounts of data
• Large memory requirements
• Few users
• Periodic requests
• Relatively long response times

When both OLTP and decision-support queries are running on the same computer, the database server must balance its resources so that all users receive the best possible performance.