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.

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.

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

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.

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.