Inefficient case stmt

I have a section in a sproc's WHERE statement that is causing the sproc to
be inefficient, but I don't know why.
When I look at the execution plan when this line is included, it shows that
it does a table scan on hcpfn_el3parser - which does return a table. The
part that is confusing to me, is that it does a table scan when @.el3 is
null. As you can clearly see below, when @.el3 is null, it shouldn't even
get to the function. But it does - why? And can I rewrite it in a better
way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
seconds if I rem out this line.
and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
dbo.hcpfn_el3parser ( @.el3 )) end )
Thanks, AndreTry,
what data type is dl.el3?
AMB
"Andre" wrote:

> I have a section in a sproc's WHERE statement that is causing the sproc to
> be inefficient, but I don't know why.
> When I look at the execution plan when this line is included, it shows tha
t
> it does a table scan on hcpfn_el3parser - which does return a table. The
> part that is confusing to me, is that it does a table scan when @.el3 is
> null. As you can clearly see below, when @.el3 is null, it shouldn't even
> get to the function. But it does - why? And can I rewrite it in a better
> way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
> seconds if I rem out this line.
> and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
> dbo.hcpfn_el3parser ( @.el3 )) end )
> Thanks, Andre
>
>|||@.el3 varchar(72) = null|||>> The part that is confusing to me, is that it does a table scan when
@.el3 is null. As you can clearly see below, when @.el3 is null, it
shouldn't even get to the function. <<
Wrong. A CASE expression has to evaluate *all* the THEN clauses to
determine the data type of the expression.
If you post some DDL and the actual code,then someone can help. I am
sure that names like "el3" are clear and meaningful in your industry
and just some silly sequential numbering used to fake an array, but
none of us can read it.
The obvious thing is that you need to get rid of "hcpfn_el3parser ()"
and use a query. You should start writing SQL that looks like SQL
instead of OO or procedural code.|||On Tue, 17 May 2005 10:16:56 -0700, Andre wrote:

>I have a section in a sproc's WHERE statement that is causing the sproc to
>be inefficient, but I don't know why.
>When I look at the execution plan when this line is included, it shows that
>it does a table scan on hcpfn_el3parser - which does return a table. The
>part that is confusing to me, is that it does a table scan when @.el3 is
>null. As you can clearly see below, when @.el3 is null, it shouldn't even
>get to the function. But it does - why? And can I rewrite it in a better
>way? My sproc runs in 17 seconds with this line (and a null @.el3), or 7
>seconds if I rem out this line.
>and dl.el3 in ( case when @.el3 is null then dl.el3 else ( select el3 from
>dbo.hcpfn_el3parser ( @.el3 )) end )
>Thanks, Andre
>
Hi Andre,
Hard to tell without knowing more about your tables, data and the
function. But you might try if this works:
AND ( @.el3 IS NULL
OR dl.el3 = (SELECT el3 FROM dbo.hcpfn_el3parser (@.el3) )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)