Solved

exists/not exists select 1 vs select *

Posted on 2010-11-23
11
1,497 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
[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
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Part Two of the two-part Q&A series with MalwareTech.
OnPage enhanced its integration with ConnectWise Manage to offer incident responders more control over the ticket and Incident Resolution Lifecycle.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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