CODEWITHSUNDEEP
sqlsql-serverperformancecount
Artur Udod
Artur Udod

Reputation: 4753

Strange performance of SELECT COUNT(1)

I have a select query with some complex joins and where conditions and it takes ~9seconds to execute.

Now, the strange thing is if I wrap the query with select count(1) the execution time will increase dramatically.

SELECT COUNT(1) FROM 
(
    SELECT .... -- initial query, executes ~9s
) 
-- executes 1min

That's very strange to me, since I would expect an opposite result - the sql-server engine should be smart enough to optimize the inner query execution (for instance, do not execute nested queries in the select clause, etc).

And that's what execution plans comparison shows! It says it should be 74% to 26% (the former is initial query and latter is wrapped with select count(1)).

But that's not what really happens.

Idk if I should post the query itself, since it's rather large (if you need it then just let me know in comments).

Thaks you!)

Upvotes: 1

Views: 136

Answers (1)

Martin Smith
Martin Smith

Reputation: 453628

When you use count(1) you no longer need all the columns.

This means that SQL Server can consider different execution plans using narrower indexes that do not cover all the columns used in the SELECT list of the original query.

Generally this should of course lead to a leaner, faster, execution plan however looks like in this case you were unlucky and it didn't.

Probably you will find a node with a large discrepancy between actual and estimated rows - this kind of thing will propagate up in the plan and can lead to sub optimal choices of strategy for other sub trees (e.g. sub optimal join orderings or algorithms)

Upvotes: 1

Related Questions