Question

1.write about your plan for the database performance tuning, query optimization, and distributed database management systems.  Include appropriate diagrams.

Answer 1

Database performance tuning:

Performance tuning can be difficult when it comes to databases. Especially when one is dealing with a large-scale database. There are different ways to achieve it though. Some of these ways are explained below:

1. Using Indexes:

Indexes are the best way to fine tune the performance. This is because they guarantee that there will be no same values. It is also a vast topic. The structure of indexes helps in improving the data retrieval speed and other database operations. There are also many random lookups and ordered records to work with. Once the index has been used, the rows can be sorted faster.

1. Creating plan for execution:

It is also important to have an execution plan. In the plan one can display the method of data retrieval using graphics. From the plan one can also check index details. Having an execution plan is the best way to avoid any type of loops in the coding.

1. Correlated subqueries:

One should always try to avoid correlated subqueries. These queries make use of parent query values. It is usually run one row at a time. Outer query returns the row for the result. This execution decreases the performance. To get an easy route around, it is important to structure these queries in the proper way.

Query optimization:

Query optimization is the best way to improve execution performance. The execution plan and the candidate plans are picked with less cost. The cost computation is the big part of request information.

1. Cost based:

In the cost based situations, most efficient statements are picked. For the non procedural queries, optimizer is allowed to merge for free and process in different orders. The statistics are gathered about the data and the optimal plan is examined based on index scan.

1. Execution based:

Execution based method relies on the plan for execution. Different steps are used to either retrieve data from the rows or issue different statements based on the entire plan. The plan comparisons can help in deciding the internal units picked.

Distributed DBMS:

While creating a plan for a distributed database, one has to decide on the portion of the overall database that will be stored in a specific location. This is because such a system will be distributed in different locations.

One can create different fragments for such a plan. The metadata or information about all the fragments are stored in a data catalogue.