Tuesday, February 22, 2011

count (*) every where.......

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(*) ...



Tuesday, February 15, 2011

# Temporary tables and "select into"

This is again one of those myth where developer think that  using select into much faster then insert into  table command.


It is not!!! in fact insert into is faster in  lot of scenarios.



Overall we should avoid using "select into" especially when selecting into temporary tables and also when you are dealing with large resultset.This is because select into locks the system tables.
 
 when you are doing "select into #" temporary tables, we are locking system objects in tempdb database. This can prevent other users from creating temporary tables unless you are done.
 
Imagine if your inserting into temp table some subset from the table which has million rows,the system tables will be locked for the amount of time that insertion is finish...
 
Please use simple "Insert statement" instead of "select into"