Declaration of variables improve or decrease performance

Question

So I am trying to improve my SQL, one topic that arose, does declaration help with performance or not so like for instance lets say you want to do a if else statement but you need to know if the count is higher than 0 for example right

SELECT @COUNTER = COUNT(ID) FROM tblSome WHERE NUMBER > TOTAL

IF(@COUNTER > 0)

OR would it be better than something like this

IF((SELECT @COUNTER = COUNT(ID) FROM tblSome WHERE NUMBER > TOTAL)>0)

I am just trying to minimize the time it takes, but also it would be nice to know

For now I cannot really find a difference with the small amounts of data I am using and I am not sure how to test it further or go test it to the next level

Answer 1

Use of variables can help or hinder performance dependent on the exact circumstances. There isn't a single rule.

In this case the use of the separate variable assignment step can be harmful.

It gives the optimiser no choice but to count all the rows as it doesn't look ahead to how you use that variable in future statements.

using an IF (SELECT COUNT(*) ...) > 0 allows the optimiser to see that it is true as long as the count is >=1 and can sometimes be optimised to an IF EXISTS (semi join) and stop reading after a single row.

But you are better off just writing it as EXISTS anyway rather than relying on that.

(The discussion on this internet archived blog post has more about the circumstances where this optimisation might happen)