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
Subqueries can be less efficient than joins in many cases; this change is not a first step.
- ✓
Check the query execution plan and optimize join order
Why this is correct
Analyzing the execution plan reveals performance bottlenecks and suggests whether indexes, join order, or other optimizations are needed.
- ✗
Add indexes to all columns used in joins
Why it's wrong here
Adding indexes without analyzing the plan may not help and can degrade write performance; it should be based on plan analysis.
- ✗
Increase server memory
Why it's wrong here
Increasing memory is a hardware solution that should be considered after query optimization.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DA0-002 question from scratch — 986 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.