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
... There are 10 kinds of people. Those who know binary code, and those
who don't.I'm going to step out a limb here...
Does this not return the same result?
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
A
Why bother with the join if this is what you're doing?
Cheers,
Jason Lepack
On Aug 1, 5:35 am, Henning Eiben <ei...@.busitec.de> wrote:
> 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 =3D 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: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =
Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Jason Lepack wrote:
> I'm going to step out a limb here...
> Does this not return the same result?
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> A
> Why bother with the join if this is what you're doing?
>
actually I have a view
CREATE VIEW dbo.SomeView AS
SELECT A.*, B.*
FROM
A LEFT OUTER JOIN B ON
A.id = B.id
and my SQL-Statement looks like this:
SELECT DISTINCT
A.a, dbo.sp_concat(A.b, A.c) as x
FROM
dbo.SomeView
This way I can create the select-clause in my app, and since I'm using
the view in the from-clause, I don't have to deal with the join ...
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
... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||But the join is still there...
I have no idea what you are trying to do. You call your function
concat, which I think means concatenate, but then all you do is select
the first of the two values that isn't null, that isn't concatenation.
If you want assistance then you have to give information about what
you are actually trying to do.
Cheers,
Jason Lepack
On Aug 1, 10:01 am, Henning Eiben <ei...@.busitec.de> wrote:
> Jason Lepack wrote:
>
>
> actually I have a view
> CREATE VIEW dbo.SomeView AS
> SELECT A.*, B.*
> FROM
> A LEFT OUTER JOIN B ON
> A.id =3D B.id
> and my SQL-Statement looks like this:
> SELECT DISTINCT
> A.a, dbo.sp_concat(A.b, A.c) as x
> FROM
> dbo.SomeView
> This way I can create the select-clause in my app, and since I'm using
> the view in the from-clause, I don't have to deal with the join ...
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =
Saalmann
> --
> ... If it wasn't for C, we would be using BASI, PASAL and OBOL!|||Henning,
Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
all thy troubles are solved :-)
Maybe in some weird twisted way, the optimizer thinks it cannot rule out
the use of table B if it is unknown whether the UDF accesses B.
Gert-Jan
Henning Eiben wrote:
> 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
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.|||Gert-Jan Strik wrote:

> Henning,
> Interesting case. Add WITH SCHEMABINDING to your UDF's definition, and
> all thy troubles are solved :-)
Wow! That did it!

> Maybe in some weird twisted way, the optimizer thinks it cannot rule out
> the use of table B if it is unknown whether the UDF accesses B.
Seems that SQL2005 is more cautious than SQL2000
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
... If it wasn't for C, we would be using BASI, PASAL and OBOL!