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)

Indexes

when dropping and recreating indexes, is there anything else that needs to
be done?
I keep hearing the phrase rebuilding indexes...
thanks
What we in the SQL Server world refer to as rebuilding an index means that we try to defragment the
index, for performance reasons.
This is typically done using the DBCC DBREINDEX command (in 2005 we use ALTER INDEX with the REBUILD
option instead). This means that SQL Server under the covers create a new index and then drop the
old one.
An alternative method is DBCC INDEXDEFRAG (ALTER INDEX with the REORGANIZE option). This work
differently internally.
See Books Online for details of what these commands does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g" <gregoranton_nospamplease_@.hotmail.com> wrote in message news:an3If.8988$bd4.6488@.edtnps84...
> when dropping and recreating indexes, is there anything else that needs to be done?
> I keep hearing the phrase rebuilding indexes...
> thanks
>
|||Thanks Tibor!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OK5j2UMMGHA.1760@.TK2MSFTNGP10.phx.gbl...
> What we in the SQL Server world refer to as rebuilding an index means that
> we try to defragment the index, for performance reasons.
> This is typically done using the DBCC DBREINDEX command (in 2005 we use
> ALTER INDEX with the REBUILD option instead). This means that SQL Server
> under the covers create a new index and then drop the old one.
> An alternative method is DBCC INDEXDEFRAG (ALTER INDEX with the REORGANIZE
> option). This work differently internally.
> See Books Online for details of what these commands does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "g" <gregoranton_nospamplease_@.hotmail.com> wrote in message
> news:an3If.8988$bd4.6488@.edtnps84...
>