Question

Suppose your company has built a database application that runs on a centralized database, but even with a high-end computer and appropriate indices created on the data, the system is not able to handle the transaction load, leading to slow processing of queries. What would be some of your options to allow the application to handle the transaction load?

Answer 1

In a centralized system, to handle the transaction load, query performance has to be improved.
Query processing should be done keeping in mind the below points:

• Reducing the amount of memory and storage required for processing of the query.
• Maximizing Throughput which means that the number of requests should that are processed in a given amount of time should be increased.
• Response Time of the queries has to be reduced.
• Memory and Storage required for processing has to be reduced.

Query Optimization can be done by following the below steps:

1. Generating Query Tree: The root of this tree represents the whole query and leaf nodes are the tables of the query. While execution, the internal nodes are processed and at last the root is executed which produces the fianl result table.
2. Query Plan Generation: This provides with the access path which define how the relational operations should be performed in a tree. The plan also tells how the temporary and intermediate tables should be processed.
3. Code Generation: Code Generation is highly dependent on the Operating System. Here in the final step Execution Manager runs the query and performs the final result.

Hence we can generate all the query plans using the above 3 steps and choose the most optimized one.

A few more rules to be kept in mind:

• The restrictive select operations should be performed before the other operations.
• Performing Select before the Join operation reduces the number of tuples that are required for joining.
• Cross Product Operations should be avoided as they result in huge intermediate tables that will require time for processing.