Solved

count vs exists

Posted on 2016-08-11
9
59 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
ID: 41752206
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 46

Expert Comment

by:Vitor Montalvão
ID: 41752363
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
ID: 41752372
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41752375
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:enrique_aeo
ID: 41752391
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41752432
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 46

Accepted Solution

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

Author Comment

by:enrique_aeo
ID: 41752448
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
ID: 41752454
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

943 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

7 Experts available now in Live!

Get 1:1 Help Now