Solved

count vs exists

Posted on 2016-08-11
9
47 Views
Last Modified: 2016-08-11
Hi experts

i have this code
if exists
 (
      select 1
      from sales.SalesOrderDetail
      where ProductID = 970
 )
 print 'Exists = True'

 declare @rowcount int
 set @rowcount =
 (
      select count(*)
      from sales.SalesOrderDetail
      where ProductID = 970
 )
 if @rowcount > 0
 print 'Exists = True'

I need an example where you can check with the execution plan  or statistical is best to use EXISTS instead of COUNT
0
Comment
Question by:enrique_aeo
  • 4
  • 4
9 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
Not sure about the example, but remembering my training EXISTS is more efficient, as once the query engine finds a single row that meets the condition it will move on, whereas COUNT needs to consume the entire dataset and get the aggregated count, which takes longer.
1
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Jim is right but if you need to proof something then add SET STATISTICS IO ON to the beginning of the script so you can have the necessary information for reporting:

SET STATISTICS IO ON
...
--your code here
...
SET STATISTICS IO OFF
0
 

Author Comment

by:enrique_aeo
Comment Utility
this is results

Table 'SalesOrderDetail'. 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.
Exists = True
Table 'SalesOrderDetail'. 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.
Exists = True


set statistics io on
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

but i do not understand
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
The table is already in cache on the 2nd interaction so you should clean the buffers between each interaction.
Also if the table is very small (like one extent data page) there's no difference between them since all records will be loaded into memory.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:enrique_aeo
Comment Utility
set statistics io on
DBCC FREEPROCCACHE
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

DBCC FREEPROCCACHE
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. 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.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. 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.
Exists = True
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
Also if the table is very small (like one extent data page) there's no difference between them since all records will be loaded into memory.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
Btw, you didn't clean any data buffer but stored procedure only. The correct command is DBCC DROPCLEANBUFFERS.
0
 

Author Comment

by:enrique_aeo
Comment Utility
set statistics io on
DBCC DROPCLEANBUFFERS
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

DBCC DROPCLEANBUFFERS
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. 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.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. 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.
Exists = True
0
 

Author Comment

by:enrique_aeo
Comment Utility
it's OK

 set statistics io on
DBCC DROPCLEANBUFFERS
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
       )
       print 'Exists = True'

DBCC DROPCLEANBUFFERS
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. 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.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 228, physical reads 1, read-ahead reads 226, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now