database performacne

Question 1

What is database performance?

Answer 1

Database performance refers to the effectiveness of database systems in managing data operations, measured by response time, throughput, and resource utilization.

Question 2

Why is database tuning important?

Answer 2

1. Improves user experience
2. Optimizes resource usage
3. Ensures consistency and reliability

Question 3

What is database design in the context of performance tuning?

Answer 3

Database design involves structuring the database to reduce redundancy, ensure data integrity, and enhance performance through optimal schema, table, column, relationship, and constraint management.

Question 4

What are the consequences of bad database design?

Answer 4

1. Redundancy
2. Update anomalies
3. Inefficiency
4. Scalability issues
5. Loss of flexibility

Question 5

What is indexing?

Answer 5

Indexing is the process of creating a data structure to improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

Question 6

When should indexes be used?

Answer 6

1. Columns frequently used in the WHERE clause
2. Columns used in JOIN conditions
3. Columns used for sorting data in ORDER BY clauses

Question 7

What is query optimization?

Answer 7

Query optimization involves rewriting queries, choosing efficient execution paths, and using database features like indexes and partitioning to reduce the time and resources required to execute SQL queries.

Question 8

What are the principles of query optimization?

Answer 8

1. Understanding execution plans
2. Proper use of indexes
3. Query rewriting
4. Keeping statistics and cardinality estimates up to date
5. Leveraging database features like partitioning and materialized views

Question 9

Provide an example of a simple optimization by filtering early.

Answer 9

Bad:
SELECT * FROM sales WHERE year = 2023;
Better:
SELECT customer_id, total FROM sales WHERE year = 2023;

Question 10

What is the advantage of using SQL functions?

Answer 10

SQL functions encapsulate complex operations into reusable blocks of code, improving performance, ensuring consistency, and maintaining data integrity by centralizing logic within the database.

Question 11

Provide an example of a SQL function to check order limits.

Answer 11

CREATE OR REPLACE FUNCTION check_order_limit()
RETURNS TRIGGER AS $$
DECLARE
total_orders_for_day NUMERIC;
BEGIN
SELECT SUM(amount) INTO total_orders_for_day
FROM orders
WHERE customer_id = NEW.customer_id AND
date_trunc(‘day’, order_date) = date_trunc(‘day’, NEW.order_date);

IF total_orders_for_day + NEW.amount > 5000 THEN
    RAISE EXCEPTION 'Total orders for the day exceed the £5,000 limit.';
END IF;

RETURN NEW; END; \$\$ LANGUAGE plpgsql;

CREATE TRIGGER check_order_before_insert
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION check_order_limit();

Question 12

What is the importance of monitoring and continuous improvement in database performance tuning?

Answer 12

Continuous monitoring helps identify performance bottlenecks, inefficient queries, and data integrity issues, allowing for proactive issue resolution and maintaining optimal database performance.

Question 13

What key areas should be monitored for database performance?

Answer 13

1. Query performance
2. Resource utilization (CPU, memory, disk I/O)
3. Index usage and efficiency
4. Errors and warnings

Question 14

What is the continuous improvement cycle for database performance tuning?

Answer 14

1. Assess
2. Plan
3. Implement
4. Review