DBMS and Query Optimization. Flashcards
Discuss basic database performance tuning concepts
Database performance-tuning: A set of activities and procedures designed to reduce the response time of a
database system.
what is SQL performance tuning
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.
what is DBMS performance tuning
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
Explain how the DBMS processes SQL queries - 1. Parsing
Parsing - The DBMS parses the SQL query and chooses the most efficient access/execution plan.
Explain how the DBMS processes SQL queries - 2. Execution
Execution - The DBMS executes the SQL query using the chosen execution plan.
Explain how the DBMS processes SQL queries - 3. Fetching
Fetching - The DBMS fetches the data and sends the result set back to the client.
Explain the role of indexes in speeding up data access - indexes
Indexes: Used to speed up data access by creating ordered sets of values that contain index keys and pointers
to actual table rows.
Explain the role of indexes in speeding up data access - Data sparsity
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
Different types of indexes:
- 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.
Discuss the query optimiser
Query optimisation: Crucial during the parsing phase of query processing, where a DBMS decides the best
approach to execute a query.
two types of query optimizers - 1. Rule-Based Optimizer
Rule-Based Optimizer: Uses predefined rules to determine the best approach to execute a query.
two types of query optimizers - 1. Rule-Based Optimizer
Cost-Based Optimizer: Uses statistics about database objects to determine the most efficient query
execution plan
Describe common practices used to write efficient SQL code
- 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
Formulate queries and tune the DBMS for optimal performance
Steps to formulate a query:
- Identify what columns and computations are required
- Identify source tables
- Determine how to join the tables
- Determine what selection criteria are needed
- Determine the order in which to display the output
SQL Performance Tuning
SQL performance tuning: Focuses on writing efficient SQL code from the client perspective, recognizing that
most modern relational DBMSs offer automatic query optimization.
Key Considerations: in SQL performance returning. - Automatic
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.
Key Considerations: in SQL performance returning. - performance
Performance Impact: A well-structured SQL query can vastly outperform a poorly structured one,
addressing most performance issues related to SQL code.
Database performance tuning
DBMS performance tuning: Includes managing memory allocation and physical storage to optimize database
performance.
Key Tuning Areas in Database Performance Tuning
- 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.
what is an in-memory database system
In-Memory Database Systems: These systems store data in primary memory (RAM) to reduce disk access
bottlenecks, enhancing performance for modern applications.
