SQL SERVER – Single Table Scan for Multiple Aggregated Operators

3 years ago

The way Comprehensive Database Performance Health Check, works is very interesting. First, we focus on the server configuration, database configuration, and right following that we start focusing on the area where we identify performance bottlenecks. The very last stage of the Performance Tuning is the Query Tuning and while looking at it, we found a very interesting scenario. Today we will discuss how a single table scan for multiple aggregated operators helps to improve performance.

Multiple Table Scan

While looking at the slow running queries we found a query which was taking plenty of the resources for our server and practically was creating the resource bottleneck.

Here is the simplified query:

SELECT (SELECT MAX([OrderDate])
FROM [Sales].[Orders]) mx,
(SELECT
MIN([BackorderOrderID])
FROM [Sales].[Orders]) mn;

Here is the output of the statistics IO and execution plan.

Table ‘Orders’. Scan count 2, logical reads 1384

Now the query was actually aggregating two different columns from the same table and it contained two different subqueries. This was actually doing two times scan to your same table. It can be easily fixed by re-writing the queries.

Single Table Scan

It is not necessary to write two different queries to do two different aggregate operations on the table. Here is how you can write a single script to do that.

SELECT MAX([OrderDate]) mx,
MIN([BackorderOrderID]) mn
FROM [Sales].[Orders];

Now when you run the above script, it will do a single scan to the table and also it will read the lesser data from the table. Here is the output of the statistics IO and execution plan.

Table ‘Orders’. Scan count 1, logical reads 692

A simple trick like this one immediately improves the performance of the query. Our query started to run in just half of the time than before and overall many such instances improved overall performance of the system.