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 (AT) busitec (DOT) de


+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax

Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de

Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann



--
.... ERROR: CPU not found.

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 ;

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:

Well, I simplified the UDF ... in my actual application the UDF is a
little more complex.

Well - in the meantime I figured that "WITH SCHEMABINDING" in the UDF
does the magic.

--

Henning Eiben

busitec GmbH
Consultant

e-mail: eiben (AT) busitec (DOT) de


+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax

Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de

Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann



--
.... Gentlemen: Start your debuggers...