Solved

count vs exists

Posted on 2016-08-11
9
62 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 47

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 47

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 47

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 47

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

776 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