Query Optimization

Hi,
I have a DB-based application, which has a UDF like this:
CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@.A,@.B)
END
Using SQL-Server 2000 I execute the following statement:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
Since the statement is only selecting columns from table "A", it is
being optimized so that the join with table "B" is not being executed.
Because table "B" is quite large, this saves quite some execution-time.
When this statement is being executed on a SQL-Server 2005 the join is
being executed, resulting in a much longer execution-time. This seems to
be because of the UDF, because if this is being left out, the optimizer
eliminates the processing of table "B".
Background: I have a view, which consists of a lot of joins of several
tables, and I dynamically build the select-clause of the statement in my
application. Because the optimizer only processes the tables that are
actually being used in the select-statement this is an easy way to not
deal with the joins in the application itself.
But why is the use of the function changing the behavior of the 2005
optimizer?
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... ERROR: CPU not found.
>> CREATE FUNCTION fn_concat(@.A varchar(255), @.B varchar(255))[vbcol=seagreen]
This seems like a typical case of UDF abuse. Is there a real need for an UDF
for such simple cases? Why not simply write your SQL statement as:
SELECT A.a, COALESCE( A.b, A.c )
FROM A LEFT JOIN B
ON A.id = B.id ;
[vbcol=seagreen]
The optimizer in SQL 2000 and SQL 2005 are different and this could be one
of the processing differences due to an internal optimization routine called
constant folding, which is pretty common in most modern DBMSs.
Anith
|||Anith Sen wrote:

> This seems like a typical case of UDF abuse. Is there a real need for an UDF
> for such simple cases? Why not simply write your SQL statement as:
Well, I simplified the UDF ... in my actual application the UDF is a
little more complex.

> SELECT A.a, COALESCE( A.b, A.c )
> FROM A LEFT JOIN B
> ON A.id = B.id ;
>
> The optimizer in SQL 2000 and SQL 2005 are different and this could be one
> of the processing differences due to an internal optimization routine called
> constant folding, which is pretty common in most modern DBMSs.
Well - in the meantime I figured that "WITH SCHEMABINDING" in the UDF
does the magic.
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Strae 59
48157 Mnster
www.busitec.de
Sitz der Gesellschaft: Mnster
HR B 55 75 - Amtsgericht Mnster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschftsfhrer: Simon Bwer, Henning Eiben, Stefan Khn, Martin Saalmann
... Gentlemen: Start your debuggers...