Dynamic SQL replace with CASE statement.

Hello All,
I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
The code that I replaced it with
seems to be worst than the original code. Would any one know why.
The first batch of SQL statements (Dynamic Sql) is the original code and I
tried to replace this code with the
code in the second batch. If you notice, I have used a CASE statement to
mimic the IF part of the code. But
in the Execution Plan I see a Clustered Index scan in the replaced code but
in the execution plan of the original
code I see a Index s. I had to put the OPTION statement to avoid
parellelism
Would any one know why it is doing an Index S in the Original case and
Clustered Index Scan in the replaced
code ?
Gopi
USE Northwind
GO
Declare @.str varchar(200)
SET @.str = 'select * from Orders '
Declare @.i int
set @.i = 10252
IF @.i <> 0
SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
Print @.str
Exec(@.str)
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN OrderID
ELSE @.i
END
OPTION (MAXDOP 1)
GO
select * from Orders where OrderID = 10252
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = @.i
GOHello rgn,
I have a feeling that this may help.
If not then get back to me.
http://support.microsoft.com/defaul...kb;en-us;841627
Peter
"Status quo, you know, that is Latin for "the mess we're in."
Ronald Reagan
"rgn" wrote:

> Hello All,
> I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
> The code that I replaced it with
> seems to be worst than the original code. Would any one know why.
> The first batch of SQL statements (Dynamic Sql) is the original code and I
> tried to replace this code with the
> code in the second batch. If you notice, I have used a CASE statement to
> mimic the IF part of the code. But
> in the Execution Plan I see a Clustered Index scan in the replaced code bu
t
> in the execution plan of the original
> code I see a Index s. I had to put the OPTION statement to avoid
> parellelism
> Would any one know why it is doing an Index S in the Original case and
> Clustered Index Scan in the replaced
> code ?
> Gopi
> USE Northwind
> GO
> Declare @.str varchar(200)
> SET @.str = 'select * from Orders '
> Declare @.i int
> set @.i = 10252
> IF @.i <> 0
> SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
> Print @.str
> Exec(@.str)
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = CASE
> WHEN @.i = 0 THEN OrderID
> ELSE @.i
> END
> OPTION (MAXDOP 1)
> GO
> select * from Orders where OrderID = 10252
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = @.i
> GO
>
>
>|||Peter,
The query is not based on a View if you noticed.
<<. The max degree of parallelism configuration option for your instance of
SQL Server is set to 0 or to a number that is greater than 1.
. The query is based on a view.>>
I would understand if the query is Slow had I used operators such as <> ...
like '%xyz%'
as it makes sense to scan the table. But I dont understand why it has to do
a Clustered
Index Scan because I introduced a CASE statement to avoid dynamic SQL.
Gopi
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
> Hello rgn,
> I have a feeling that this may help.
> If not then get back to me.
> http://support.microsoft.com/defaul...kb;en-us;841627
> Peter
> "Status quo, you know, that is Latin for "the mess we're in."
> Ronald Reagan
>
>
> "rgn" wrote:
>|||My apologies for both the rubbish earlier post and what I'm going to say now
.
I know what cause the execution path difference, but not why.
I did a test where the OrderID was a clustered index with 10000 rows
populated by identity.
If you change the where clause so instead of it returning OrderID, it
returns say 0, then you get a clustered index s, similar to having the
statement without the CASE Statement, so there is something about 'ID' that
causes a difference.
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN 1
ELSE @.i
END
Anyway sorry I can't be more of a help, probably one of the really big
brains know the answer.
Peter
"It's true hard work never killed anybody, but I figure, why take the chance
?"
Ronald Reagan
"rgn" wrote:

> Peter,
> The query is not based on a View if you noticed.
> <<. The max degree of parallelism configuration option for your instance o
f
> SQL Server is set to 0 or to a number that is greater than 1.
> .. The query is based on a view.>>
> I would understand if the query is Slow had I used operators such as <> ..
.
> like '%xyz%'
> as it makes sense to scan the table. But I dont understand why it has to d
o
> a Clustered
> Index Scan because I introduced a CASE statement to avoid dynamic SQL.
> Gopi
>
> "Peter 'Not Peter The Spate' Nolan"
> <PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
> news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
>
>