DBMS and Query Optimization.

Question 1

Discuss basic database performance tuning concepts

Answer 1

Database performance-tuning: A set of activities and procedures designed to reduce the response time of a
database system.

Question 2

what is SQL performance tuning

Answer 2

SQL performance tuning – on the client side – that will generate an SQL query to return the correct answer in
the least amount of time, using the minimum amount of resources at the server end.

Question 3

what is DBMS performance tuning

Answer 3

DBMS performance tuning – on the server side – that will properly configure the DBMS environment to
respond to clients’ requests in the fastest way possible, while making optimum use of existing resources

Question 4

Explain how the DBMS processes SQL queries - 1. Parsing

Answer 4

Parsing - The DBMS parses the SQL query and chooses the most efficient access/execution plan.

Question 5

Explain how the DBMS processes SQL queries - 2. Execution

Answer 5

Execution - The DBMS executes the SQL query using the chosen execution plan.

Question 6

Explain how the DBMS processes SQL queries - 3. Fetching

Answer 6

Fetching - The DBMS fetches the data and sends the result set back to the client.

Question 7

Explain the role of indexes in speeding up data access - indexes

Answer 7

Indexes: Used to speed up data access by creating ordered sets of values that contain index keys and pointers
to actual table rows.

Question 8

Explain the role of indexes in speeding up data access - Data sparsity

Answer 8

Data sparsity: It’s the variety of unique values in a column. Helps determine whether to use an index.
Low sparsity columns – Less suitable for indexing
High sparsity columns – Benefits from indexing

Question 9

Different types of indexes:

Answer 9

• Hash indexes: Used for fast lookups based on equality.
• B-tree indexes: A balanced tree structure, ideal for columns with many unique values.
• Bitmap indexes: Use bit arrays, suited for columns with few repeating values, common in data
  warehousing.

Question 10

Discuss the query optimiser

Answer 10

Query optimisation: Crucial during the parsing phase of query processing, where a DBMS decides the best
approach to execute a query.

Question 11

two types of query optimizers - 1. Rule-Based Optimizer

Answer 11

Rule-Based Optimizer: Uses predefined rules to determine the best approach to execute a query.

Question 12

two types of query optimizers - 1. Rule-Based Optimizer

Answer 12

Cost-Based Optimizer: Uses statistics about database objects to determine the most efficient query
execution plan

Question 13

Describe common practices used to write efficient SQL code

Answer 13

• Numeric field comparisons are faster than character, date, and NULL comparisons
• Equality comparisons are faster than inequality comparisons (>,<)
• Transform conditional expressions to use literals (text or numbers instead of variables)
• Write equality conditions first when using multiple conditional expressions
• When using multiple AND conditions, write the condition most likely to be false first
• When using multiple OR conditions, put the condition most likely to be true first
• Avoid the use of NOT logical operator

Question 14

Formulate queries and tune the DBMS for optimal performance
Steps to formulate a query:

Answer 14

1. Identify what columns and computations are required
2. Identify source tables
3. Determine how to join the tables
4. Determine what selection criteria are needed
5. Determine the order in which to display the output

Question 15

SQL Performance Tuning

Answer 15

SQL performance tuning: Focuses on writing efficient SQL code from the client perspective, recognizing that
most modern relational DBMSs offer automatic query optimization.

Question 16

Key Considerations: in SQL performance returning. - Automatic

Answer 16

Automatic Optimization: Most current-generation relational DBMSs automatically optimize queries,
but these optimizations are general and not tailored to the specific circumstances of query execution.

Question 17

Key Considerations: in SQL performance returning. - performance

Answer 17

Performance Impact: A well-structured SQL query can vastly outperform a poorly structured one,
addressing most performance issues related to SQL code.

Question 18

Database performance tuning

Answer 18

DBMS performance tuning: Includes managing memory allocation and physical storage to optimize database
performance.

Question 19

Key Tuning Areas in Database Performance Tuning

Answer 19

• Data Cache: Configure data cache size to allow for maximum request servicing.
• SQL Cache: Store parsed SQL statements to speed up repeated queries by using the same access plan.
• Sort Cache: Allocate temporary storage for operations requiring sorting.
• Optimizer Mode: Understand whether the DBMS uses a cost-based or rule-based optimizer, and manage
  statistics accordingly.

Question 20

what is an in-memory database system

Answer 20

In-Memory Database Systems: These systems store data in primary memory (RAM) to reduce disk access
bottlenecks, enhancing performance for modern applications.