Question

1. Explain how the physical design of a database influences performance tuning.

2.Explain three ways queries can be altered to increase database performance.

3. Present specific examples to illustrate how implementing each query alteration could optimize the database.

Answer 1

solution;

Question 1: Explain how the physical design of a database influences performance tuning.

Answer: In Physical Design we select a physical schema, which is a collection of data structures for implementing the conceptual schema.

In Tuning we periodically adjust the conceptual schema and/or physical schema of a system in order to adapt to the changing requirements.

Good physical design impacts the tuning by altering the database workload. Changes made to physical/conceptual schemas can impact all the updates and queries in the workload. Sometimes it is desirable to target some specific queries and applications.

Changes in physical design of the database can help in :-

a. Making the application run faster

b. Reducing the response time of transactions and queries

c. In improving the overall throughput of the transactions.

Question 2 and Question 3

1. Properly creating Indexes

By performing indexing carefully, we have easily make our SQL queries perform better. Indexes allow us to have quicker access to the database in complex situations. Users often ignore indexes or try to index everything. Both of these approaches are not right. Our queries will process slow without the indexes and with everything indexed, our updates and triggers will be ineffective.

2. Retrieve only the required data

We retrieve almost all the tuples or rows and columns using *, even though there is no requirement of all the rows or columns.

If our table is of small size, then writing a query to retrieve all the columns or rows will be of not use. But for large data sets, retrieving only required tuples, columns or rows will help in saving a lot of computational expenses.

For e.g. Writing a query like this (Below), will add to computational costs;

Instead we can retrieve selective columns, such as;

We can also make use of "LIMIT" clause for selective retrieval.

3. To avoid functions in Left hand side of the Operator

Although functions provide a quicker solution to handle complex tasks and can be easily used in "SELECT" clause and "WHERE" clause. But usage of functions in "WHERE" clause can cause serious computational issues. Check the example given below

;

Although there is usage of Index on the appointment date column of the table, the query will not be able to perform a full table scan because of the application of the DATEDIFF function. If the output of the applied function is evaluated at run time, the server visits all the rows of the table for retrieving the required data. But if we make changes in our code (Below), we can achieve great results.

SELECT nickname FROM users WHERE appointment_date > '2015-04-30';

Here we have not applied any function in where clause, hence the system can fully utilize the index to seek the data in an efficient way