Solved

exists/not exists select 1 vs select *

Posted on 2010-11-23
11
1,430 Views
Last Modified: 2012-06-27
Hi, i understand select * has a worse performance than select 1 as it needs to expand the columns etc..

But is there any difference logically between those sql, and are there ever an instance where you must use select * instead of select 1.  

select * from table1 where not exists  (select 1 from table2)
vs
select * from table1 where not exists  (select * from table2)

thanks
0
Comment
Question by:gagaliya
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Expert Comment

by:alpmoon
ID: 34198259
I can't think of any case which make a difference between two queries. I am curious to hear if anyone argues it.
0
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 100 total points
ID: 34198277
On most databases, the performance difference is very small (but it does exist).  I don't think there is ever a case where you need to use "select *" rather than "select 1".  At least, I've never run into one and I can't think of any.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 250 total points
ID: 34201586
Supporting wilcoxon, it does make a difference, and sometimes that difference can be significant enough to care about.

If the subquery table in the example is very deep (many rows) and very wide (many and/or large columns) then the difference between "select 1" and "select *" can really add up.

In the case where the table width is 1952 bytes and there are 100M rows, then "select *" has to do I/O on 100M rows x 1952 bytes = 181.79Gb. Whereas if we do "select 1", the row width is now effectively 1 byte, which means the total I/Os are 100M rows x 1 byte = 95.36Mb, or roughly 0.0005 x the I/O of "select *". 181Gb vs 95Mb is a pretty big difference in I/O, and you would definitely see a difference in performance. Consider too the likely effects on data cache even if MRU replacement was used.

I am exaggerating for effect, it's seldom we'd see such a big difference, but anything on the inside of a join or within an expression should be optimised as far as possible because those differences do add up.

Even if you argue that the example above is going to table scan either way, consider the case where there are indexes. Without a WHERE clause, "select *" must read the full rows, but ASE is smart enough to use the narrowest NC index for "select 1" - effectively this is a special case of NC index "covering" (obviously we don't need to read the actual data rows when we don't care about any of the columns, thus every NC index covers "select 1").
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 34203868
Hi Joe,

if I understand you correctly, this means, that the most effective solution would be:

select * from table1 where not exists  (select top 1 1 from table2)

This should need only 1 byte IO (1 row x 1 byte). Right ?
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 34203870
> This should need only 1 byte IO (1 row x 1 byte). Right ?

I mean just for subquery.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 34203996
The "top" would normally require sorting which would likely slow things down. I know it doesn't look on first glance like "select 1" would need sorting but I am fairly sure "top" involves the idea of row order - unlike, say, the effects of "set rowcount 1" which stops processing after the result set has 1 row.
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 34207119
Physical performance difference is obvious. I thought that we are discussing whether there is a case they are different logically. I can't imagine a where clause which makes them logically different even though it may include a join clause between table1 and table2:

select * from table1 where not exists  (select 1 from table2 where ......)
vs
select * from table1 where not exists  (select * from table2 where ......)

I am still curious to hear if anyone argues it.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 34208716
If you're saying would those two most recent examples have a different query plan, for table scans no I don't believe so. NC index covering per my earlier comment could produce a different query plan for the inner query though.
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 150 total points
ID: 34209012
When it is said "logically", I thought we are talking about the result set rather than query plan or physical performance.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 250 total points
ID: 34209680
Oh! My mistake. Yes, absolutely zero difference to overall result set.
0
 
LVL 1

Author Closing Comment

by:gagaliya
ID: 34249640
Yes i was mostly asking the logical difference in the resultset.

Thank you guys, it's very clear now.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

21 Experts available now in Live!

Get 1:1 Help Now