EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008)

Question

EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No idea, sorry.

If you just do a straight Select 1 FROM a, do you get the same error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 2

Yes, the same error is raised.

I posted a comment on the blog, maybe Conor will be able to explain this.

Thanks!

Best Regards,

Chris Büttner

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

Probably a fairly minor point but even a very very small difference can be worth it when you have a piece of code being run thousands of times a second.

Also another good place to use a 1 instead of a * is when doing a count.

For example

SELECT COUNT(1) FROM sysobjects

vs

SELECT COUNT(*) FROM sysobjects

Again a fairly minor difference but one that can have a large impact if the code is run often enough.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

satvir.grewal03 Valued Member Points: 65 More actions · Answer 4

Hi All

I have a question like if we use

(SELECT top 1 1 from) instead of (SELECT 1 from) will it contribute in some sort of improvement.

I am talking about EXISTS here.

Thanks

Satvir

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

It will not.

Please in future post new questions in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

I know it is a very old thread, however, quite often, this exactly discussion comes up in different work places...

Is anyone have any material evidence that one syntax is better/faster than another?

If the BoL description for COUNT(*) is not enough (where it is kind of stated that * - doesn't refer to columns in the COUNT function...), try execution plan:

SET SHOWPLAN_ALL ON

GO

SELECT COUNT(1) FROM sysobjects

SELECT COUNT(*) FROM sysobjects

GO

Any difference? I could see none! Check the underlying aggregate function - it is COUNT(*).

If not convinced, try to mesure time:

create table #results (tmms bigint)

go

declare @i bigint

declare @st datetime

set @st = getdate()

select @i = count(*) from dbo.YourLargestTable

insert #results select datediff(ms, @st, getdate())

go 100

select avg(tmms) from #results

Anything? Not even "very small difference"!

I've played quite few matches on very large partitioned and non-partitioned tables in 2005, 2008 and 2012 SQLServer...

My WorldCup's results:

COUNT(*) vs COUNT(1) = draw!

The same goes for EXISTS...

SELECT 'OK' WHERE EXISTS(SELECT 1 FROM sysobjects)

SELECT 'OK' WHERE EXISTS(SELECT NULL FROM sysobjects)

SELECT 'OK' WHERE EXISTS(SELECT * FROM sysobjects)

It doesn't matter what you use, SQL always performs Constant Scan, the rest (index or not index or which index) would be determined by the WHERE or JOIN conditions if any inside of EXISTS query.

So, looks like it is pure personal preference.

I would really appritiate if some one could teach me better (and has reproducible tests to prove its course)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Would a post from Gail's blog help?

To TOP or not to TOP an EXISTS[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Luis Cazares (6/17/2014)
Would a post from Gail's blog help?
To TOP or not to TOP an EXISTS[/url]

Sorry Luis, help with what?

First of all it is a bit different topic. Second the conclusion there does look the same to me:

The Exists operator itself tries to retrieve jus the absolute minimum of information, so the addition of TOP 1 does nothing except add 5 characters to the query size.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Eugene Elutin (6/17/2014)
Luis Cazares (6/17/2014)
Would a post from Gail's blog help?
To TOP or not to TOP an EXISTS[/url]
Sorry Luis, help with what?
First of all it is a bit different topic. Second the conclusion there does look the same to me:
The Exists operator itself tries to retrieve jus the absolute minimum of information, so the addition of TOP 1 does nothing except add 5 characters to the query size.

I didn't remember exactly the content and made the mistake. But remembered the conclusion and seemed as an additional set of tests for the way that EXISTS work.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Yep, Gail's article conclusion tells that it doesn't matter what you type between SELECT and FROM in EXISTS query, as long it represents valid T-SQL sytax, SQL server always does exactly the same thing.

I will not be surprised if SQL query engine (parser or compiler) strips all things typed there and replaces it with some litteral constant (most likely "SELECT *") before performing the query.

So far, all suggestions that one sytax performs better than another are just unproved speculations...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]