Performance - Subqueries vs case statements

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 2000 Forums

SQL Server Development (2000)

Performance - Subqueries vs case statements

Author

Topic

myjunk
Starting Member

2 Posts

Posted - 2006-08-19 : 15:09:18

Another question to decide which is better performance wise, using case statements, whenever possible or using subqueries?

Currently my query runs like this.

select
TableA.ColumnA,
TableB.ColumnA,
TableC.ColumnA
from
(select
A
from
table
where
...
)TableA
inner join
(select
A
from
table
where
...
)TableB
on tableA.columnA = tableB.columnA
inner join
(select
A
from
table
where
...
)TableC
on tableC.columnA = tableB.columnA

or something like this

select
case when A="0" then columnA else null end as A_0,
case when A="1" then columnA else null end as A_1,
case when A="2" then columnA else null end as A_2
from
tableA
where
...

I know that subqueries impact performance/resources since they mean scanning the table (for each subquery??? vs case statements which scans only 1 time???)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 00:41:45

If you are trying for cross-tab like output, then second method is the way to go

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources