IF Exists() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

IF Exists()

Author

Topic

CanadaDBA

583 Posts

Posted - 2004-10-21 : 08:07:39

What's the difference between the following codes?


IF Exists( SELECT * FROM tblTemp )

and


IF Exists( SELECT TOP 1 * FROM tblTemp )

Is the second one faster (optimizer)?

But if the IF EXISTS( SELECT * ... ) returns true as soon as SELECT * ... found the first record. If this SELECT provides ALL the records then we can say it can take more time than the second one. Am I right?

Canada DBA

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 08:25:10

The "*" in an EXISTS statement is supposed to be a hint to the optimiser to do what-it-likes to optimise the task - thus supposed to be the "fastest syntax". But I aint tested it!

Krsiten

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-21 : 09:50:21

They will (probably) give the same query plan but select * is easier to type and read and is the standard syntax for an if exists.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CanadaDBA

583 Posts

Posted - 2004-10-21 : 11:57:46

A friend notified me that the EXIST() function, only checks whether at least one row "exists" that meets the criteria. Also, another friend told me that using TOP could be even slower!

Anyhow, it seems using without TOP is easier and SQL Server takes care of the rest and optimizes it for better results.

Canada DBA

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 12:13:01

[code]IF Exists( SELECT * FROM tblTemp )[/code]
Is the correct way to do this.
And the only time * should be used.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:13:26

"And the only time * should be used"

Got your flame-proof underware on, have you?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 13:27:17

Meant "select *" of course...
flame-proof, frost-resistant and water-proof

/rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-21 : 13:52:56

The TOP 1 would be redundant. This is similiar to:

WHERE ID IN (SELECT DISTINCT ID FROM SomeTable)

There is no need to add "DISTINCT" in there -- SQL does that automatically.

Good point about performance: if you add an ORDER BY so the TOP 1 becomes "meaningful", then it will perform slower.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 14:20:09

"SQL does that automatically"

I won't argue, but that's a surprise - it would involve a sort, and knowledge that there are likely to be duplicates [otherwise the sort is just time-wasted].

Also, some of our IN clauses have been optimised by ordering the subquery so that most-frequently-occuring keys are first - which suggests that IN is not then re-sorting the list (or our test was pants, which could quite easily be the case!)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-21 : 14:54:16

i've always noticed that adding/removing DISTINCT has never changed the execution plan that I could notice.

select * from orders
where OrderID in (select orderID from [order details])

select * from orders
where OrderID in (select distinct orderID from [order details])

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-22 : 02:08:11

I had a look at the plan for

select * from Suppliers
where CompanyName in (select CompanyName from Shippers)

(About the only thing that I could find where the sub-selects wasn't covered by an index!), and the query plan explicity does a distinct:


|--Compute Scalar(DEFINE:([Suppliers].[HomePage]=[Suppliers].[HomePage]))
   |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Suppliers]))
      |--Nested Loops(Inner Join, OUTER REFERENCES:([Shippers].[CompanyName]))
         |--Sort(DISTINCT ORDER BY:([Shippers].[CompanyName] ASC))
         |  |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Shippers].[PK_Shippers]))
         |--Index Seek(OBJECT:([Northwind].[dbo].[Suppliers].[CompanyName]), 
              SEEK:([Suppliers].[CompanyName]=[Shippers].[CompanyName]) ORDERED FORWARD)

SO maybe its even more worthwhile putting an ORDER BY to force most-commonly-occuring to the top of the list - i.e. where SQL is going to do a ORDER BY to get DISTINCT anyway, where I know the values are distinct anyway I might as well order by something that improves performance.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources