Solved

exists/not exists select 1 vs select *

Posted on 2010-11-23
11
1,449 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sybase license issue 6 534
Looking for Sybase 12.5.4 EBF ESD#9.1 or 10 patch 5 787
VB6 to VB.NET transition 1 492
SyBase Query Syntax Case When 7 127
Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 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