Solved

exists/not exists select 1 vs select *

Posted on 2010-11-23
11
1,477 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
[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
SQL Substring Extraction 4 564
Crystal Reports VB6 11 1,095
compare Sybase and MSSQL server 18 1,006
restore master and user database in sybase ase 2 110
Determining the an SCCM package name from the Package ID
Invest in your employees with these five simple steps to improve employee engagement and retention.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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