Post reply

(X OR Y) vs. IN (X,Y)

  • December 20, 2006 at 1:21 am

    #115539

    Hi,

    which of these two statements executes faster ?

    WHERE <<FIELDNAME>> = (X OR Y)

    or

    WHERE <<FIELDNAME>> IN (X,Y)

    Might be a nice one for QOD....

     

    Regards

    Jurriaan

  • December 20, 2006 at 3:15 am

    #679071

    No difference. the query plans for the two are identical. In fact, checking the query plan, the query parser expanded the second out into ORs.

    I tried these two

    SELECT

    * FROM sysobjects WHERE xtype='P' OR xtype = 'S' OR xtype = 'U'

    SELECT

    * FROM sysobjects WHERE xtype IN ('P','U','S')

    Identical reads, time, query cost. Checking the predicate of the clustered indx scan that was done, they were the same for both. 

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 20, 2006 at 3:25 am

    #679072

    SELECT * FROM sysobjects WHERE xtype='P' OR xtype = 'S' OR xtype = 'U'

      |--Compute Scalar(<Removed for brevity> )

           |--Clustered Index Scan(OBJECT: ([master].[dbo].[sysobjects].[sysobjects]), WHERE: (([sysobjects].[xtype]='P' OR [sysobjects].[xtype]='S') OR [sysobjects].[xtype]='U'))

    SELECT * FROM sysobjects WHERE xtype IN ('P','U','S')

      |--Compute Scalar(<Removed for brevity> )

           |--Clustered Index Scan(OBJECT: ([master].[dbo].[sysobjects].[sysobjects]), WHERE: (([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') OR [sysobjects].[xtype]='P'))

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 20, 2006 at 5:00 am

    #679081

    Thanks.

    I don't have access to SQL Query analyzer at the moment.

    The IN looks like the way to go if the list of values becomes a bit longer.

    Happy holidays

    Jurriaan

  • December 20, 2006 at 8:58 am

    #679145

    Now MY answer would be that the one using IN would work faster. Why? Because the first one would fail for a syntax error.

    Jurriaan asked about this...

    WHERE <> = (X OR Y)

    NOT this:

    WHERE <> = X OR <> = Y

    Makes a difference.

    -SQLBill

Viewing 5 posts - 1 through 4 (of 4 total)

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