SQL Server Performance

Using Select * Vs Select ID, FName, LName...

Discussion in 'T-SQL Performance Tuning for Developers' started by pavankan, Jan 15, 2004.

  1. pavankan New Member

    I have read in one of the posts that use Select * is inefficient..

    I was wondering what the experts' opinion would be...[8D]

    You see, when we do need to select all columns, would it result in a performance gain if we'd list all the coloumns as against usig a SELECT * ?

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  2. Luis Martin Moderator

    I think no at all.

    But, wait for development experts post.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Twan New Member

    There would be no difference in performance if you select all columns anyway. However using select * from within an asp page, can result in the page breaking after adding a column to the table (in the same way as once you join two tables you should always prefix every column with the table's name or alias)

    Cheers
    Twan
  4. Richard Beacroft New Member

    You may be confusing selects like the following:

    if exists (select * from .......)

    with

    if exists (select 1 from .......)

    The latter one is marginally quicker as select 1 does not require the column names to be evaluated.

    Rik
  5. FrankKalis Moderator

  6. pavankan New Member

    How about:

    select count(*) from someTable

    AGAINST

    select count(ID) from someTable

    wouldn't the second one be way faster than the first?

    Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

  7. gaurav_bindlish New Member

    I think latter statement would leave ot the cases where ID is null whereas count(*) won't.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. Twan New Member

    Pretty sure that if there is a unique index on a non-null column in the table, then there is no difference between the two statements, as SQL will use the unique index in both cases

    Cheers
    Twan
  9. bambola New Member

    COUNT(*) will count all rows including nulls
    COUNT(field) will count all rows that are not null

    COUNT(field) when field is indexed will usually be faster than count(*)
    COUNT(field) with clustered index might be slower than count(field) with a non-clustered index (less reads)

    as for exists statements

    if exists (select * from... )
    if exists (select 1 from... )
    if exists (select NULL from... )

    I used to think the last 2 perform better but they all generate the same execution plan.
  10. Twan New Member


    I have not been able to see any difference between count(*) and count(field) on my tables...? both use the same plan and each take eactly the same time to execute... In fact they use an index on a field that is not mentioned in the query...?

    it is almost as if when the column is not null, then both count(*) and count(field) will use the smallest index to get the rowcount...?

    Twan



  11. bambola New Member

    you are right, Twan. As long as the column is not null they will use the same execution plan and the samllest index. maybe something had changed since the last time I've tested it, or maybe I'm just getting old! <img src='/community/emoticons/emotion-5.gif' alt=';-)' />
  12. Twan New Member

    <br />I don't think you're getting old! <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />I also thought that count(field) was quicker, as I thought that if exists( select 1 was quicker. but both of these seem to have been resolved in SQL2k...<br /><br />Twan
  13. gaurav_bindlish New Member

    Hey! No body mentioned about count(ID) not counting the rows which have value as NULL.

    This may be a critical factor on SELECT performance if the column under consideration is nullable.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  14. Twan New Member

    Hi Gaurav,<br /><br />yeah you mentioned it yourself earlier on... and we put an assumption in that the column is declared as not null for the purpose of the rest of the discussion <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  15. gaurav_bindlish New Member

    I think I am getting older for this... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1">

Share This Page