If Exists vs Count(*) which is best

IF EXISTS vs COUNT(*)

Disclaimer: I’m in no way suggesting that you should replace all Exists with Count, this is just an example which worked for me.

if exists (

select * from dbo.sService ss

inner join dbo.RuleUsage bru on ss.RuleUsageID = bru.RuleUsageID

inner join dbo.sRule br on br.RuleID = bru.RuleID

inner join dbo.sale isi on isi.SaleID=ss.SaleID

inner join dbo.iSale inS on InS.SaleID = isi.SaleID and inS.SaleID=ss.SaleID

where br.RuleID=@RuleID

and inS.ProductID = @ProductID

)

select 0

else

select 1

Table 'sRule'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'iSale'. Scan count 594687, logical reads 3,197,254, physical reads 2456, read-ahead reads 1837, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sSale'. Scan count 1, logical reads 2354, physical reads 17, read-ahead reads 3624, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RuleUsage'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

v It is claimed that the optimiser applies special rules to IF EXISTS which makes this type of query optimal.

v In fact for as far back as I can remember there have been examples and forum posts on which is quicker or best or optimal or…….

v As usual I believe the reality is that “ It just depends “ to coin an oft used phrase.

ü What does tend to be true is that behaviour seems to change from version to version of SQL Server.

ü Experience shows that single IF EXISTS seem to work fine, it’s when there is a join that performance issue occur.

The example here is a sanitised version which is why it can’t be demonstrated live, in actual fact with no data in cache on an 8 core server with a fast disk system it takes close to a minute for the original query to run , on a laptop I fear it might never finish.

The three largest tables contain 21 million, 11 million and 3 million rows; the query had effective indexes to cover the join and sarg

Here’s the deployed solution

select @count = COUNT_BIG(*) from dbo.sService ss

inner join dbo.RuleUsage bru on ss.RuleUsageID = bru.RuleUsageID

inner join dbo.sRule br on br.RuleID = bru.RuleID

inner join dbo.sale isi on isi.SaleID=ss.SaleID

inner join dbo.iSale inS on InS.SaleID = isi.SaleID and inS.SaleID=ss.SaleID

where br.RuleID=@RuleID

and inS.ProductID = @ProductID option (maxdop 16 );

if ( @count )>0

select 0

else

select 1

--end

Table 'iSale'. Scan count 9, logical reads 8517, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RuleUsage'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sSale'. Scan count 45, logical reads 164,025, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sRule'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

ü COUNT_BIG is 8 bytes vs 4bytes for count.

( Yes for some reason this query generates more than 2,147 million rows )