SELECT * vs. SELECT 1

I have always understood that when using the WHERE EXISTS clause, one
codes it as WHERE EXISTS (SELECT * FROM .....).  Lately, I've been told
that performance is better if the it's coded as ...(SELECT 1 FROM ...)
(or SELECT '' .. - that is, to select some literal).  Everything I've
read says that SELECT * is used with EXISTS because there's no result
set returned - it only evaluates into true or false.

I'm now curious - is there an advantage to writing SELECT 1 instead of
SELECT * with EXISTS?  If so, how much of an advantage, & why do all of
the SQL books I've seen (included the Transact-SQL documentation) say to
write SELECT * ?

Thanks to all.


0
Lorraine
7/30/1998 4:48:06 PM
sybase.sqlserver.performance 1181 articles. 0 followers. Follow

5 Replies
783 Views

Similar Articles

[PageSpeed] 17

Lorraine,
Have you noticed that Sybase Central will convert "Select *" into a full
Select with all the column names from the table?
As soon as I noticed that, I started coding "Select 1"...

Paul Horan
VCI
Springfield, MA

Lorraine Brenner wrote in message <35C0A3C5.BBEE35E5@timeinc.com>...
>I have always understood that when using the WHERE EXISTS clause, one
>codes it as WHERE EXISTS (SELECT * FROM .....).  Lately, I've been told
>that performance is better if the it's coded as ...(SELECT 1 FROM ...)
>(or SELECT '' .. - that is, to select some literal).  Everything I've
>read says that SELECT * is used with EXISTS because there's no result
>set returned - it only evaluates into true or false.
>
>I'm now curious - is there an advantage to writing SELECT 1 instead of
>SELECT * with EXISTS?  If so, how much of an advantage, & why do all of
>the SQL books I've seen (included the Transact-SQL documentation) say to
>write SELECT * ?
>
>Thanks to all.
>
>


0
Paul
7/31/1998 2:36:11 PM
Thanks for the info.

Paul A. Horan wrote:

> Lorraine,
> Have you noticed that Sybase Central will convert "Select *" into a full
> Select with all the column names from the table?
> As soon as I noticed that, I started coding "Select 1"...
>
> Paul Horan
> VCI
> Springfield, MA



0
Lorraine
7/31/1998 4:14:18 PM
Lorraine Brenner wrote:
> 
> I have always understood that when using the WHERE EXISTS clause, one
> codes it as WHERE EXISTS (SELECT * FROM .....).  Lately, I've been told
> that performance is better if the it's coded as ...(SELECT 1 FROM ...)
> (or SELECT '' .. - that is, to select some literal).  Everything I've
> read says that SELECT * is used with EXISTS because there's no result
> set returned - it only evaluates into true or false.
> 
> I'm now curious - is there an advantage to writing SELECT 1 instead of
> SELECT * with EXISTS?  If so, how much of an advantage, & why do all of
> the SQL books I've seen (included the Transact-SQL documentation) say to
> write SELECT * ?
> 
> Thanks to all.

Probably not what you want to hear, but ...

Since Sybase (and most other vendors, too!!) have a tendency to change
the operation of their optimizer from release to release ... and Paul's
suggestion was for SQL Anywhere while you may be running Adaptive Server
(i.e., different products hence different optimizers) ... I'd suggest
you run some tests.

If you're on Adaptive Server look at comparing the results of 'set
statistics io on' for both queries; also look at comparison of 'set
showplan on'.  Do you see any differences?

'set statistics io on' is neat to watch ... it shows you the number of
times that the table is 'logical' accessed as an object, it also shows
you lots of i/o counts for each of the logical accesses.  Count up total
i/o's ... see which one comes out better?

-- 
Mark A. Parsons
Iron Horse, Inc.
0
Mark
8/9/1998 6:44:21 PM
On Sun, 09 Aug 1998 14:44:21 -0400,
 in sybase.public.sqlserver.performance+tuning
Mark Parsons <iron_horse@compuserve.com> wrote: 
>Lorraine Brenner wrote:
>> 
>> I have always understood that when using the WHERE EXISTS clause, one
>> codes it as WHERE EXISTS (SELECT * FROM .....).  Lately, I've been told
>> that performance is better if the it's coded as ...(SELECT 1 FROM ...)
>> (or SELECT '' .. - that is, to select some literal).  Everything I've
>> read says that SELECT * is used with EXISTS because there's no result
>> set returned - it only evaluates into true or false.
>> 
>> I'm now curious - is there an advantage to writing SELECT 1 instead of
>> SELECT * with EXISTS?  If so, how much of an advantage, & why do all of
>> the SQL books I've seen (included the Transact-SQL documentation) say to
>> write SELECT * ?
>> 
>> Thanks to all.
>
>Probably not what you want to hear, but ...
>
>Since Sybase (and most other vendors, too!!) have a tendency to change
>the operation of their optimizer from release to release ... and Paul's
>suggestion was for SQL Anywhere while you may be running Adaptive Server
>(i.e., different products hence different optimizers) ... I'd suggest
>you run some tests.
>
>If you're on Adaptive Server look at comparing the results of 'set
>statistics io on' for both queries; also look at comparison of 'set
>showplan on'.  Do you see any differences?
>
>'set statistics io on' is neat to watch ... it shows you the number of
>times that the table is 'logical' accessed as an object, it also shows
>you lots of i/o counts for each of the logical accesses.  Count up total
>i/o's ... see which one comes out better?
>
>-- 
>Mark A. Parsons
>Iron Horse, Inc.

I have noticed that usually SQL server CPU time and overall execution time is
better with "select 1". 
But I have not seen any improvement in logical reads , or scan counts . I think,
this is because we are not reducing # rows by substituting * with 1 ... correct
? 
Anyway , I would use select 1 , especially when checks to exist in tables with
many columns.

-Jayaraj
0
Jayaraj
8/27/1998 7:14:10 PM
I think the difference between select * and select 1 is that one has to 
go through syscolumns checking and the other one does not.  

On Thu, 27 Aug 1998 15:14:10 -0400,
 in sybase.public.sqlserver.performance+tuning
Jayaraj Nayanaveettil <nayanaj@tvratings.com> wrote: 
>On Sun, 09 Aug 1998 14:44:21 -0400,
> in sybase.public.sqlserver.performance+tuning
>Mark Parsons <iron_horse@compuserve.com> wrote: 
>>Lorraine Brenner wrote:
>>> 
>>> I have always understood that when using the WHERE EXISTS clause, one
>>> codes it as WHERE EXISTS (SELECT * FROM .....).  Lately, I've been told
>>> that performance is better if the it's coded as ...(SELECT 1 FROM ...)
>>> (or SELECT '' .. - that is, to select some literal).  Everything I've
>>> read says that SELECT * is used with EXISTS because there's no result
>>> set returned - it only evaluates into true or false.
>>> 
>>> I'm now curious - is there an advantage to writing SELECT 1 instead of
>>> SELECT * with EXISTS?  If so, how much of an advantage, & why do all of
>>> the SQL books I've seen (included the Transact-SQL documentation) say to
>>> write SELECT * ?
>>> 
>>> Thanks to all.
>>
>>Probably not what you want to hear, but ...
>>
>>Since Sybase (and most other vendors, too!!) have a tendency to change
>>the operation of their optimizer from release to release ... and Paul's
>>suggestion was for SQL Anywhere while you may be running Adaptive Server
>>(i.e., different products hence different optimizers) ... I'd suggest
>>you run some tests.
>>
>>If you're on Adaptive Server look at comparing the results of 'set
>>statistics io on' for both queries; also look at comparison of 'set
>>showplan on'.  Do you see any differences?
>>
>>'set statistics io on' is neat to watch ... it shows you the number of
>>times that the table is 'logical' accessed as an object, it also shows
>>you lots of i/o counts for each of the logical accesses.  Count up total
>>i/o's ... see which one comes out better?
>>
>>-- 
>>Mark A. Parsons
>>Iron Horse, Inc.
>
>I have noticed that usually SQL server CPU time and overall execution time is
>better with "select 1". 
>But I have not seen any improvement in logical reads , or scan counts . I
think,
>this is because we are not reducing # rows by substituting * with 1 ... correct
>? 
>Anyway , I would use select 1 , especially when checks to exist in tables with
>many columns.
>
>-Jayaraj

Alex Yuen
Hong Kong Bank Markets IT
0
Alex
8/30/1998 1:49:17 PM
Reply: