Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Rule of thumb for "IN' vs. "OR"?

    Howdy all. I havent posted much here and thought I'd get everyones opinion. It seems to me that to design a table to use an IN clause instead of multiple OR clauses, the IN would usually produce faster query times. Would you agree? Here is what I mean. If I can design a table with 1 coulmn to represent all program types, or have each program type be a seperate coulmn, the single column could be written to use an IN statement instead of an OR statement. Of course the single column table could potentiall require up to 3 row of data, but the query would be different, which I suspect would be faster. Example:

    CREATE TABLE [dbo].[StatusTypeWith3ProgramTypes] (
    [IDStatusType] [int] IDENTITY (1, 1) NOT NULL ,
    [StatusType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AR] [bit] NULL ,
    [SR] [bit] NULL ,
    [CI] [bit] NULL
    ) ON [PRIMARY]
    GO

    create TABLE [dbo].[StatusTypeWith1ProgramType] (
    [IDStatusType] [int] IDENTITY (1, 1) NOT NULL ,
    [StatusType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    ProgramType [char] (2)
    ) ON [PRIMARY]
    GO


    select * from StatusTypeWith3ProgramTypes
    where StatusType = 'foo'
    and (ar = '1' or ci = '1')

    select * from StatusTypeWith1ProgramType
    where StatusType = 'foo'
    and ProgramType in('ar','ci')

    As you see, each StatusType could have multiple ProgramTypes, which is the cause of this question. Obviously this is a very simple example, but serves the purpose. Thanks to all for any insight.
    Thanks in advance.
    ChrisR

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You are in the wrong forum, as the answer is DBMS-dependent.

    In theory the two are the same and should perform identically. But in practice it depends on the sophistication of your DBMS's optimiser.

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    My apologies. I had assume all DBMS' that use an SQL lanuage would optimize the same way.

    Thanks.
    Thanks in advance.
    ChrisR

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by MSSQL DBA
    My apologies. I had assume all DBMS' that use an SQL lanuage would optimize the same way.

    Thanks.
    I can understand how you'd make the assumption, but I can't think of any two SQL engines that optimize the same way. Very few are even close, even when they share a common ancestor.

    -PatP

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by MSSQL DBA
    It seems to me that to design a table to use an IN clause instead of multiple OR clauses, the IN would usually produce faster query times.
    The whole point of having an optimizer is that it should free you from worrying about things like this so you can perfect your design.

    Once you have your design worked out you can do profiling to find out what's *really* slowing things down (or chewing up memory/disk). The real performance hits are never where you expect them because the complex interactions (especially when you throw users in the mix...) within a live application are far too complicated for mere mortals to predict.

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Do you need to enforce mutual excluvisity on your program types? Will there ever be additional program types in the future? Do you need to store additional information about your program types, such as description? It seems to me that programs would be a candiate for a separate entitiy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •