DA0-002 Data Analysis Practice Question
A data analyst is reviewing a SQL query that joins three large tables. The query takes over an hour to run. The analyst notices that the WHERE clause filters on indexed columns in only two tables. Which of the following should the analyst do first to improve performance?
⚠ Common exam trap
CompTIA often tests the misconception that adding indexes or hardware is the immediate fix, when in fact analyzing the execution plan and adjusting join order is the cheapest and most effective first step.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Check the query execution plan and optimize join order
The query execution plan reveals how the database engine processes joins and filters. By checking the plan, the analyst can identify the most selective filter and rearrange the join order to reduce the number of rows processed early, which is the most impactful first step. Optimizing join order leverages existing indexes without requiring schema changes or hardware upgrades.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use subqueries instead of joins
Why it's wrong here
Rewriting joins as subqueries does not reduce the work the optimiser performs; it often prevents effective join reordering and predicate pushdown, so the hour-long scan persists. Subqueries suit correlated lookups or existence checks, not replacing joins between three large tables.
- ✓
Check the query execution plan and optimize join order
Why this is correct
The execution plan reveals how the optimiser orders and joins the three tables, including scan and join methods. Since only two tables have indexed filter columns, examining the plan identifies whether join order or a missing index causes the hour-long runtime.
- ✗
Add indexes to all columns used in joins
Why it's wrong here
Indexing every join column adds write overhead and storage without addressing the unfiltered table's full scan; the stem already flags missing WHERE-clause indexes as the bottleneck. Broad join-column indexing suits read-heavy analytical workloads where join predicates, not filters, dominate cost.
- ✗
Increase server memory
Why it's wrong here
Adding RAM does not create the missing index on the third table's filter column, so the query still scans it fully; memory only helps when the working set exceeds buffer pool. Increasing server memory suits workloads bottlenecked on disk I/O for cached pages, not missing indexes.
About these practice questions
Courseiva writes every DA0-002 question from scratch — 1,004 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DA0-002 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DA0-002 exam.