SQL Server, the "Distinct clause" competition. Oops i did it again! ...from 24 seconds to 6 milliseconds!

Hi guys,

Thank you all mates, the last post was very successful and therefore I propose its continuation. I recommend you read it before reading this post.

We were left to the point of using the feature called grouped aggregate pushdown applied to a columnstore index. The shortest run time obtained was 56 milliseconds (I remember that we started from 30 seconds ..omg)

 

Introduction

In the last post we "played" with the "Group By".

With a "Group By" clause we can use aggregate functions like COUNT, MIN,MAX,etc and perform calculation on a group of rows to return unique value:

SELECT Classifiers, COUNT(Classifiers)
FROM Movements
GROUP BY Classifiers

But if you need only to remove duplicate values and so extract distinct values we can use a DISTINCT operator we can simply write this T-SQL command:

SELECT DISTINCT Classifiers
FROM Movements

Today we apply the same concepts we saw in the last post to a query that contains a distinct operator and we will also need a little ... imagination!

oops I was forgetting ... good reading!

The DISTINCT Clause (...and the competition continues)

To get out baseline we run our query with the OPTION (MAXDOP  1)

SELECT DISTINCT Classifiers
FROM Movements 
OPTION (MAXDOP 1) 

..under the compatibility level 140:

ALTER DATABASE tests SET COMPATIBILITY_LEVEL = 140            
   

The execution time is approximately 24 seconds

       
SQL Server Execution Times:
   CPU time = 24562 ms,  elapsed time = 24588 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

The execution plan shows that the Clustered index on the ID column is used. 50 million rows are read

This is the result:

If now we remove the OPTION (MAXDOP 1) we will get an exection time of 6,5 seconds:

SQL Server Execution Times:
   CPU time = 46515 ms,  elapsed time = 6550 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

All cpus are used.

Now, let's go one step further and set the native SQL Server 2019 compatibility level 

ALTER DATABASE tests SET COMPATIBILITY_LEVEL = 150            

Now run again our query.. et voilà...

Only 1792 ms ....not bad!

SQL Server Execution Times:
   CPU time = 11721 ms,  elapsed time = 1792 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

This is the power of the Batchmode on rowstore:

Now, we can go one step further by adding a nonclustered index on the classifiers column:

CREATE INDEX IDX_MOVEMENTS_CLASSIFIERS ON MOVEMENTS(CLASSIFIERS)            

Run again our query:

 SQL Server Execution Times:
   CPU time = 36873 ms,  elapsed time = 5126 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.            

This time we are not lucky ...we are back to the standard row mode!

But we can try to drop the nonclustered index and add a columnstore index ..

CREATE COLUMNSTORE INDEX IDX_MOVEMENTS_CLASSIFIERS ON MOVEMENTS(CLASSIFIERS)            

Are you ready?

Run our query again..

Wow!! we get an impressive time of 666 ms!

 SQL Server Execution Times:
   CPU time = 656 ms,  elapsed time = 666 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.            

Columnstore is used!

Now let's play the last card using the aggregate pushdown

In order to use aggregate pushdown actually in this case we need to add a "Group by"  and the aggregate operation COUNT_BIG (*).

Our query becomes:

SELECT DISTINCT Classifiers, COUNT_BIG(Classifiers)
FROM Movements
GROUP BY Classifiers            

Execute the Query to see that the 50 million lines are no longer read:

What about the execution time? Only 48 milliseconds!!!

SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 48 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
           

Can we do better?

A bit of imagination!

As I said before this time to continue we need a bit of imagination! A different way to solve the same problem.

Imagine having the table sorted by the "classifiers" field.

We read the first row of the table and we are sure to find the first classifiers "A".

Now, still reading our ordered table we can ask SQL Server to tell us which is the first classifier greater than 'A'

Finally we repeat everything until we reach the end of the table.

Isn't it simple explained in this way?

To have the data sorted, a non-clustered index on the Classifiers column is sufficient:

       
CREATE INDEX IDX_MOVEMENTS_CLASSIFIERS ON MOVEMENTS(CLASSIFIERS)

Then to realize the logic described we need a loop or something that implements the recursion.
...and the only way in T-SQL to have recursion is to use a CTE.

The Anchor of the CTE is simply the following query:

SELECT 
  top 1 mm.Classifiers
FROM dbo.Movements AS mm
ORDER BY mm.Classifiers

While the CTE is:

;WITH CTE AS
(
SELECT 
  top 1 mm.Classifiers
FROM dbo.Movements AS mm
ORDER BY mm.Classifiers

UNION ALL

SELECT
  t.Classifiers
FROM
  (SELECT
     mm.Classifiers,
     nr = ROW_NUMBER() OVER (ORDER BY mm.Classifiers ASC) 
   FROM CTE
     JOIN dbo.Movements AS mm ON mm.Classifiers > CTE.Classifiers
  ) AS t
WHERE
  t.nr = 1
)
SELECT
    CTE.Classifiers
FROM CTE
ORDER BY
    CTE.Classifiers ASC
OPTION (MAXRECURSION 0);

Highlighted in yellow is the point where we look for subsequent classifiers.

Now execute the CTE and ....wow, only 6 milliseconds, we have a new record!

SQL Server parse and compile time: 
   CPU time = 4 ms, elapsed time = 4 ms.

(4 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 6 ms.

Completion time: 2021-11-02T19:07:34.4508052+01:00           
    

Well mates, thats all for today!
I look forward to seeing you at the next post where we will talk some theory!
If you liked this post follow me on linkedin so you will be notified as soon as a new post is published.

Luca

 

Luca Biondi @ SQLServerPerformance blog!

Next post:

Previous post: SQL Server, the "Group By" competition: from 30 seconds to 56 milliseconds