If condition Optimization help please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If condition Optimization help please

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-12-23 : 07:26:54

please see below condition and please help me this optimzed version of this.

any help much appreciated.

if @flag =1

select col1 from tbl
where a=@ and b=@b and c=@c and d=@d

if @flag= 3
select col1 from tbl
where a=@a OR b=@b

if @flag = 4
select col1 from tbl
where a=@a and d=@d

if @flag= 5
select col1 from tbl
where d= @d and b=@b

if @flag =6
select col1 from tbl
where ( a=@a and d=@d) OR (b=@b and c=@c)

if @flag =9
select col1 from tbl
where a= @a and b=@b and d=@d

if @flag =10
select col1 from tbl
where a=@a and c=@c and d=@d

if @flag =11
select col1 from tbl
where a=@a and b=@b and d=@d

if @flag =12
select col1 from tbl where a=@a and b=@b
where

if @flag =13
select col1 from tbl
where a=@a and b=@b and c=@c

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-23 : 07:53:49

Hi,

I didn't try but I feel this should cut short the code.

Select Col1 from tbl
where ((a=@a OR b=@b) and @flag= 3)
or (a=@a and b=@b and @flag= 4)
or ( d= @d and b=@b and @flag= 5)

About performance: It mainly depends on the indexes. Post the DDL
I feel it may not perform better than individual check (posted by you) but would like to here from gurus.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Sachin.Nand

2937 Posts

Posted - 2010-12-23 : 07:54:34

The flag vlaue wont be greater than 13 ?

PBUH

Kristen
Test

22859 Posts

Posted - 2010-12-23 : 08:30:17

I would have thought your code is better optimised than creating a single composite query.

Except that I would use ELSE between the IF's (as they are mutually exclusive), and I would order the IF's in order of most common usage - i.e. most common first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources