07 Query Optimization

Question 1

span_0“What is the primary bottleneck query optimization tries to reduce?”

Answer 1

I/O Cost (Input/Output). Disk access does not improve as fast as CPU speed (Moore’s Law), so minimizing block reads is the main goal.span_0

Question 2

What are the three main resources that affect query response time?

Answer 2

1. I/O (Disk access - most prominent)
2. CPU usage (Complex calculations)
3. span_1Memory usage (Cache effect)span_1

Question 3

What is a ‘Trivial Plan’ in SQL Server?

Answer 3

A plan for a simple query where there is clearly only one way to execute it. span_2The optimizer skips the complex costing process.span_2

Question 4

What happens if no Trivial Plan is found? (List the 3 Phases)

Answer 4

Phase 0: Search for simple/Hash plans (Execute if Cost < X).
Phase 1: Complex optimizations (Execute if Cost < Y).
span_3Phase 2: Parallel execution.span_3

Question 5

Difference between Logical and Physical Execution Plans?

Answer 5

Logical Plan: What the query wants to do (Relational Algebra tree).
span_4span_5Physical Plan: How the database will actually do it (Specific algorithms like Hash Join, Index Seek).span_4span_5

Question 6

What is the ‘Push Selection Down’ rule in refactoring?

Answer 6

Move the filtering (WHERE clause) as far down the tree as possible. span_6Filter the data before joining to reduce the number of rows processed early.span_6

Question 7

What is the Relational Algebra rule for splitting a selection?

Answer 7

Sigma(F AND G)(R) = Sigma(F)(Sigma(G)(R)). span_7This allows you to re-order filters.span_7

Question 8

Nested Loop Join: How does it work and what is the I/O Cost?

Answer 8

Concept: Two embedded loops (For every row in A, scan all of B).
Cost: O(Num_Block_1 * Num_Block_2).
span_8Use Case: Good for small tables.span_8

Question 9

Hash Join: How does it work and what is the I/O Cost?

Answer 9

Concept: Build a hash table in memory for the smaller table, then read larger table and search for matching records in hash table.
Cost: O(Num_Block_1 + Num_Block_2).
span_9Use Case: Good for large, unsorted tables.span_9

Question 10

Sort Merge Join: How does it work and what is the I/O Cost?

Answer 10

Concept: read both tables into memory , sort based on join columns with index
Cost: O(Num_Block_1 + Num_Block_2).
Use Case: Excellent if tables are already sorted or small.span_10

Question 11

What is a Table Scan?

Answer 11

Reading every page of the table sequentially. span_11Used when no suitable index exists or when fetching most rows.span_11

Question 12

What is a Clustered Index?

Answer 12

The data blocks are ordered by the index , preferred over table scan

Question 13

What is a Non-Clustered Index?

Answer 13

A separate structure (like a book index) that points to the data rows. span_14Reading data often requires a ‘Double Read’ (Index + Table Lookup).span_14

Question 14

What is a ‘Cover Index’ (Index with Included Columns)?

Answer 14

An index that contains all the data required by the query in its leaf nodes. span_15The database never has to look up the actual table row.span_15

Question 15

What is an ‘Index Seek’?

Answer 15

Using the B-Tree structure to jump directly to a specific record (or range start). span_16Much faster than a scan.span_16

Question 16

span_17“Why should you avoid functions in a WHERE clause (e.g.

Answer 16

WHERE YEAR(date) = 2020)?”

Question 17

span_18“Why is ‘SELECT *’ considered bad practice?”

Answer 17

It retrieves unnecessary columns, increasing I/O and preventing the use of Cover Indexes.span_18

Question 18

How does MongoDB optimization differ from SQL Server?

Answer 18

MongoDB does not use statistics to estimate cost. span_19It runs candidate plans in parallel.span_19

Question 19

How does MongoDB choose a winning plan?

Answer 19

It runs a race between plans. span_20The first plan to return 101 results wins and is cached for future use.span_20

Question 20

What is a Compound Index in MongoDB?

Answer 20

An index on multiple fields (e.g., {score: 1, date: -1}). span_21The order of fields matters for sorting and filtering.span_21

Question 21

What does ‘SARGable’ mean?

Answer 21

Search ARGument ABLE. A query written in a way that can take advantage of indexes (e.g., avoiding leading wildcards like ‘%John’).

Question 22

In Relational Algebra, when can you distribute a Selection over a Join?

Answer 22

Sigma_F(R Join S) = Sigma_F(R) Join S.
span_22You can do this only if the filter F applies only to attributes in table R.span_22

Question 23

span_23“What is the ‘Explain’ command in MongoDB?”

Answer 23

A command (query.explain()) that shows the ‘winningPlan’ and ‘rejectedPlans’ from the optimization race.span_23