SYM-400: Strategies for Database Optimization and Performance Tuning

This report, focusing on database optimization and performance tuning, explores various aspects of query optimization, including parsing, binding, and execution plan generation. It discusses tools for query optimizer strategies and performance measurement, covering factors like workload, throughput, and resource allocation. The report also examines the influence of physical design on database performance, emphasizing the importance of indexing and efficient query writing. Furthermore, it delves into query alteration techniques, such as using indexes, optimizing query performance with the EXPLAIN keyword, and limiting results in large datasets to enhance efficiency. The document provides references to support the information presented.

Running head: SYM-400
SYM-400
Name of the Student
Name of the University
Author Note

1SYM-400
Table of Contents
Phases and Functions of Query Optimizer......................................................................................2
Tools for Query Optimizer Strategies..............................................................................................2
Performance measurement...............................................................................................................3
Response time – Throughput...........................................................................................................3
Influence of Physical design over performance...............................................................................4
Query Alteration..............................................................................................................................4
Referencing......................................................................................................................................6

2SYM-400
DATABASE OPTIMIZATION AND PERFORMANCE TUNING
Phases and Functions of Query Optimizer
In order to achieve the best execution plan for a query, several steps and functions are
performed by the query processor which are described below:
 Parsing and Binding: The parsing and binding phases converts the query in a logical
tree where each node is represented by a logical operation of the query such as selecting
table or joining (Marcus & Papaemmanouil, 2018). This process leads to the generating
possible executable plans and their cost assessment.
 Generate possible execution plans: It devises a number of possible way of executing a
query to produce required results. It can be the set of physical operations defined from the
logical tree.
 Cost assessment: In this phase, the resource and time cost of the plans are generated
(Hasler, 2014). The Query Optimizer selects the lowest cost of the execution plan to
assess.
 Execution and caching: Lastly the execution engine performs the query as per the
selected plan. Later the plan is stored in the Plan cache memory.
Tools for Query Optimizer Strategies
The tools to edit the query optimizer strategies should have the properties of basic SQL
tuning, Online SQL optimizer, Database Environment, and Database in Cloud (Corlăţan et al.,
2014). The most effective and used tools are listed below:
i. SolarWinds: Database Performance Optimizer

3SYM-400
ii. Red-Gate
iii. Idera
iv. EverSQl
v. dbForge Studio for SQL Server
Performance measurement
The performance can be defined on the basis of the rate of providing information to the
uses. It can be measured according to the factors such as workload, throughput, resources,
optimization and contention. Workload distribution of the transaction, batch processes, queries
and other operations can fluctuate every second. Throughput is something that can measure the
ability of the data processing of the system. It can be measured using I/O and CPU speed.
Parallel processing can also increase the efficiency of the Database systems. Proper allocation of
the resource can improve the database also (Schmid, Galicz & Reinhardt, 2015). It can be
measured by analyzing the allocation. Next the optimization measurement can speed up the
performance. There are several tools available including Basic SQL Tuning to identify
optimization metrics. Lastly the contention is inversely proportional to the throughput which can
help measuring the performance of a database system.
Response time – Throughput
Response time is the duration between the submitting request and receiving nay response
from the system. It can be low on certain cases such as large number of interactive users to
provide acceptable responses to all of them.

4SYM-400
On other hand, throughput is calculated by the completion of processes per unit time. It is
achieved by the maximizing the processor and throughput. It is inversely proportional to the
response time and turn-around time.
Influence of Physical design over performance
The physical design of the database is developed using the data definition languages. It
highly influences the database performance whenever a query runs. It has been seen that 20% of
the data is assessed 80% of the time. During the physical design, using keys and indexes can help
in optimizing the queries (Basu et al., 2015). Joining tables and selecting columns instead of
selecting * are highly efficient. A query is more efficient when run as order of the columns
(ascending and descending both).
Query Alteration
Using Indexes: Using indexes during the creation is helpful in improving the performance.
Indexing particular fields on a table helps in minimizing the execution time of SELECT,
WHERE or HAVING operations.
Example: CREATE TABLE friends (id INT PRIMARY KEY, name
VARCHAR, city VARCHAR);
Here a unique index is created during the implementation of primary key. During the query, the
indexes are used in finding the exact rows to return as the result. It eliminates the possibility of
looking for each of the tuple.

5SYM-400
Optimizing Query performance: Placing a EXPLAIN keyword in front of a SELECT query
and its process. On criteria based and joining queries along with having indexes improves the
performance of the database.
Example: EXPLAIN SELECT * FROM friends WHERE name = 'Blake';
It shows the planning time, execution time and the rows which are excluded due to this particular
set of criteria.
Limiting the results of large databases: Using Limits can improve the performance of the
queries in large datasets.
Example: Select name, address from employees where salary>
10000 limit 10;
It will only show the first 10 rows of the query which will eliminate the rest of the data from
showing.

6SYM-400
Referencing
Basu, D., Lin, Q., Chen, W., Vo, H. T., Yuan, Z., Senellart, P., & Bressan, S. (2015, September).
Cost-model oblivious database tuning with reinforcement learning. In Database and
Expert Systems Applications (pp. 253-268). Springer, Cham.
Corlăţan, C. G., Lazăr, M. M., Luca, V., & Petricică, O. T. (2014). Query optimization
techniques in Microsoft SQL Server. Database Systems Journal, 5(2), 33-48.
Hasler, T. (2014). The cost-based optimizer. In Expert Oracle SQL (pp. 25-36). Apress,
Berkeley, CA.
Marcus, R., & Papaemmanouil, O. (2018). Towards a hands-free query optimizer through deep
learning. arXiv preprint arXiv:1809.10212.
Schmid, S., Galicz, E., & Reinhardt, W. (2015). Performance investigation of selected SQL and
NoSQL databases. AGILE 2015–Lisbon, 9-12.