Question

1. Differentiate between response time and throughput.
2. Explain how the physical design of a database influences performance tuning.
3. Explain three ways queries can be altered to increase database performance.
4. Present specific examples to illustrate how implementing each query alteration could optimize the database.

Answer 1

1) Response time: The response time is the time difference between the submission of the request until the response begins to be received. The response time must be as low as possible so that a large number of interactive user can receive an acceptable response time.

Whereas the throughput is the number of processes which are completed per unit time.Throughput is generally measured using the I/Os per second and the amount of data transferred per second.

2) The main objective of the physical designing of the database is to optimize the performance and also ensures the integrity by avoiding useless data redundancies. In the physical design, the entities are transformed into tables, the attributes into column and the instances into rows.

3) Ways through which queries can be altered to increase database performance:

1. By avoiding multiple joins in the same query. It will reduce the choices for optimizer in order to decide the join order and the join type.
2. eliminating cursors from the Query: It is always better to remove the cursors from the query and use the set-based query. The set-based query will be more efficient than the cursor-based.
3. Creation and making use of indexes: It is a well known fact that index can reduce the data retrieval time but have a reverse effect on the DML operations, which could lead to degrade in the query performance.

4) Here are few example through which the query performance can be increased:

by using indexing:

CREATE INDEX idx_user_name ON users(user_name);

By using the explain statement:

EXPLAIN SELECT city.name, city.id FROM city, country WHERE city.countrycode = country.id AND country.id = 'USA';