Post reply

Select * or Select 0 when using Exists

  • May 27, 2004 at 9:51 am

    #108438

    Has anyone any views on whether doing a

    IF Exists(select 0 from atable where...)

    is more or less efficient than

    IF Exists(select * from atable where...)

     

     


    Thanks Jeet

  • May 27, 2004 at 7:53 pm

    #508095

    ur first query always faster than 2nd one.

    in the second one u r getting all data(select *) to memory where as in the first one there was none

  • May 28, 2004 at 1:22 am

    #508120

    I usually use IF EXISTS (SELECT TOP 1 NULL FROM atable WHERE ...)

    Thus, I ensure that the minimum work is done on the server, since it is only necessary to check the existence of the first row that satisfies the search condition. Of course, if you search by a unique index, this does not make any difference.

    Regards,

    Goce Smilevski.

  • May 28, 2004 at 2:04 am

    #508130

    Actually the EXISTS( subquery ) just returns Boolean if subquery contains any rows.

    Performancewise there is no difference in the different approaches mentioned here. Sql Server does not have to bring all the rows / data into memory, just check the existance of any row in the subquery.

    All queries generate the same Execution Plan and take the same time to execute.

    /rockmoose


    You must unlearn what You have learnt

  • May 28, 2004 at 2:10 am

    #508131

    It makes no difference which method you use.  A value if true is returned as soon as a row matching the criteria has been found.

    If you still not convinced show the execution plan and run the script below in query analyser. They all generate the same execution plan.

    use northwind

    if exists (select * from dbo.Customers)

     BEGIN

     PRint '123'

     END

    IF exists (select top 1 CustomerID from dbo.Customers)

     BEGIN

     PRint '123'

     END

    IF exists (select CustomerID from dbo.Customers)

     BEGIN

     PRint '123'

     END

     

  • May 28, 2004 at 2:46 am

    #508137

    Ok, it doesn't make a difference which way to use, I didn't say it will. I only think that the way I prefer, seems to me like the clearest way to tell SQL Server what to do. Dispite the fact that SQL is clasified as a declarative language, most of the time it appears not to be 100% correct. The performance of a SQL statement depends a lot on the way it is written....so much of the declarativeness.

    Anyway, tha answer to this question is probably a matter of religion.

     

  • May 28, 2004 at 3:11 am

    #508140

    Guys,

    Thanks for the help. Much Appreciated.


    Thanks Jeet

  • May 28, 2004 at 2:16 pm

    #508294

    Similarly, I know that using count(fieldname) vs count(*) can cause a problem. By using count(*), SQLSvr's optmizer determines the best field to use based on primary keys/indexes. However, you may have a performance problem when  you specify count(fieldname) and fieldname is not indexed or does not provide the index necessary for good performance. Of coure, you will often know which field is a primary key or index. (I read this in Sajat Dam's book SQL Server Query Performance Tuning Distilled which is a great book.) 

     


    smv929

  • May 31, 2004 at 7:19 am

    #508369

    But Count(fieldName) is required in case you want to eliminate Nulls from the count. Am I right?

  • May 31, 2004 at 7:31 am

    #508371

    Well, This is straight from BOL - EXISTS

    A. Use NULL in subquery to still return a result set

    This example returns a result set with NULL specified in the subquery and still evaluates to TRUE by using EXISTS.

    USE NorthwindGOSELECT CategoryNameFROM CategoriesWHERE EXISTS (SELECT NULL)ORDER BY CategoryName ASCGO
    But yes Sunny, if you are performing a count:
count(*) will count nulls
count(fieldname) wuill not count nulls
    /rockmoose


    You must unlearn what You have learnt

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply