Solved

count vs exists

Posted on 2016-08-11
9
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 50

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
 

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 50

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 50

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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