Query Optimization

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxxxxxx


+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.
.