CompTIA Data+ DA0-002 (V2) Practice Question

The correct answer is to first filter the large table to create a smaller subset of data before applying complex operations. When dealing with very large tables, performing joins and aggregations across all records is resource-intensive and slow. By filtering the 500 million records down to only those in the 'Electronics' category from the last quarter, the subsequent, more complex parts of the query (joins and aggregations) have a much smaller dataset to process, which significantly improves performance. This can be accomplished by using a WHERE clause effectively, or by creating a temporary table or a Common Table Expression (CTE) to hold this filtered subset.

• Adding a composite index is a valid optimization technique and would speed up the process of filtering the records. However, the primary bottleneck in this scenario is the performance of the complex joins and aggregations on a massive dataset. Reducing the size of the data fed into those operations by creating a subset is the most direct and impactful optimization.
• Requesting more server memory is an infrastructure change, not a query optimization technique that the analyst would perform. Query logic should be optimized before resorting to hardware changes.
• Parameterization is an optimization technique used to allow the database to reuse query execution plans for queries that are run frequently with different values, and it helps prevent SQL injection. It does not speed up the execution of a single, long-running, ad-hoc analytical query.