It is not uncommon to come across this keywork in your tsql environment.But do we really know what exactly happens behind the scene when we use select count(*) ......?
One of the main reason I have seen developers using count(*) is to check for data existence. This is very resource intensive operation, why!!!! because count(*) has to scan all the rows in a table.
So whats the alternative?There is another keyword which you can use and that is "EXISTS". Exists hardly scan everything and stop at the first record that matches.
Such a small thing can improve lot of performance in your system.
you cacn check this yourself
Add SET STATISTICS IO and SET STATISTICS TIME ON statements and compare the output..
You will see logical reads are far less when you use EXISTS comparison to COUNT(*) ...
One of the main reason I have seen developers using count(*) is to check for data existence. This is very resource intensive operation, why!!!! because count(*) has to scan all the rows in a table.
So whats the alternative?There is another keyword which you can use and that is "EXISTS". Exists hardly scan everything and stop at the first record that matches.
Such a small thing can improve lot of performance in your system.
you cacn check this yourself
Add SET STATISTICS IO and SET STATISTICS TIME ON statements and compare the output..
You will see logical reads are far less when you use EXISTS comparison to COUNT(*) ...
No comments:
Post a Comment