SQL Server Performance

IN vs EXISTS

Discussion in 'T-SQL Performance Tuning for Developers' started by NileshRoy, Nov 27, 2007.

  1. NileshRoy New Member

    Queries with either IN or EXISTS is taking similar time. Why?
    I have following 2 queries:declare
    @ProductID as varchar(5) set
    @ProductID = 'DS'IF @ProductID IN (select OrigProductCode from productsystemmap
    where origProductCode
    in (select distinct origproductcode from CTVMoneyMarketPortfolioProduct))
    PRINT 'Y'ELSE
    PRINT 'N'GOdeclare
    @ProductID as varchar(5) set
    @ProductID = 'DS'IF EXISTS (select OrigProductCode from productsystemmap MP WHERE OrigProductCode = @ProductID
    AND EXISTS (select * from CTVMoneyMarketPortfolioProduct WHERE CTVPortfolioID IS NOT NULL AND origproductcode = MP.origproductcode))
    PRINT 'Y'ELSE
    PRINT 'N'
    Where the table definition is as follows:GOCREATE
    TABLE dbo.CTVMoneyMarketPortfolioProduct(CTVPortfolioID
    int,OrigProductCode
    char(8)
    CONSTRAINT [PK__CTVPortfolio_Product] PRIMARY KEY CLUSTERED ([CTVPortfolioID]
    ASC,[OrigProductCode]
    ASC)
    ON [PRIMARY]) ON [PRIMARY]
    GOGOCREATE
    TABLE [dbo].[ProductSystemMap]([OrigProductCode] [char]
    (8) COLLATE Latin1_General_CI_AS NOT NULL,[ProductCode] [char]
    (8) COLLATE Latin1_General_CI_AS NOT NULL,[OrigSystemID] [int]
    NULL,
    CONSTRAINT [PK_ProductSystemMap_1__16] PRIMARY KEY CLUSTERED ([OrigProductCode]
    ASC)
    ON [PRIMARY]) ON [PRIMARY]
    GO
    Both the tables do not have huge amount of data.
    The execution plan is similar, with a Index Scan on CTVMoneyMarketPortfolioProduct. Is this because of composite primary key. How can i convert this index scan to index seek?
  2. Adriaan New Member

    EXISTS will yield better performance than IN in case there are lots of matches for the subquery. So if the subquery reflects a one-to-many relationship, and there are multiple child records for most parent records, then EXISTS is probably faster. If you have only small amounts of data, you should not notice much of a difference anyway.
    EXISTS will not perform worse than IN, so it is a safe bet.
    ... and to expand on that: NOT EXISTS will perform as poorly as NOT IN.
  3. ranjitjain New Member

    In addition to Adriaan's view, you can even compare the statistics of both queries by executing
    SET STATISTICS IO ON
    before your in or exist query on bigger table and check the scan count, logical reads etc.
  4. Madhivanan Moderator

Share This Page