Which action should the Administrator take to improve the runtime and resource consumption for this query?

At a large car manufacturer, huge volumes of diagnostic data for cars are collected in the following table:

The master data for each car is stored in the following table:

Many reports require data from both tables by joining via column VehicleId.

A very frequently performed query on the system returns the number of events by FaultCode and ModelType. This query consumes many CPU and I/O resources each day.

Which action should the Administrator take to improve the runtime and resource consumption for this query?

A. Use an aggregate join index with columns FaultCode, ModelType, as well as an appropriate aggregate function.

B. Use a sparse join index with columns FaultCode and ModelType, as well as an appropriate filter function.

C. Use a NUSI on VehicleEvent.FaultCode and a NUSI on Vehicle.ModelType

D. Use a single table join index on VehicleEvent hashed by FaultCode and another single table join index on Vehicle hashed by ModelType.

View Answer

Answer: A

Explanation:

To improve the runtime and resource consumption for a query that returns the number of events by FaultCode and ModelType from the two tables VehicleEvent and Vehicle, the most appropriate action would be:

A. Use an aggregate join index with columns FaultCode, ModelType, as well as an appropriate aggregate function.

Aggregate Join Index: This type of join index will pre-join the tables VehicleEvent and Vehicle on VehicleId and store the results of frequently queried aggregations (in this case, counts by FaultCode and ModelType). It would significantly reduce the need to perform full joins and aggregations at query time, saving both CPU and I/O resources.

Option B: A sparse join index is useful for selective filtering but does not offer aggregation. Since the query involves counting (aggregation), the aggregate join index is more suitable.

Option C: Creating Non-Unique Secondary Indexes (NUSIs) on FaultCode and ModelType would help speed up searches for those columns, but it won’t help with the pre-aggregation or frequent joins that are consuming the majority of the resources.

Option D: Creating separate single table join indexes for FaultCode and ModelType on different tables won’t improve the performance of the aggregation and join-heavy query, because the problem stems from the frequent joins and aggregations, not just individual table access.