Performance considerations UDF vs Joins

Hi all,
New to SQL, but getting into it, converting Access queries to SQL.
I have a query in access which includes the following
SELECT
...
Case when A.Z is not null then C1.Y else C2.Y END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
This works fine in SQL Server, but I've also written a UDF which is
passed A.Z and returns MNO through direct reference to table C
(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
I;m only working on a small DB, so performance is not an issue, but
I'm curious as to which should have better performance considerations
so I know which way to take things. The UDF certainly makes things
tidyier, so I will stick with it unless there's a reason not to.
Thanks,
MattMatt
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
Does ther UDF accept a parameter?
(Untested)
SELECT .......
CASE
WHEN EXISTS (
SELECT * FROM C WHERE PK = Value)
THEN C1.Y
ELSE C2.Y
END AS MNO
...
FROM (A INNER JOIN
(B LEFT JOIN C C1 ON B.Z = C1.PK)
ON A.PK = B.PK)
LEFT JOIN C C2 ON A.Z = C2.PK;
PS. Have a look at execution plan of yours query?
You can make it as a stored procedure which will accept a parameter so I
gave an idea .
"Matt Bolton" <m3it@.technologist.com> wrote in message
news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
> Hi all,
> New to SQL, but getting into it, converting Access queries to SQL.
> I have a query in access which includes the following
> SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
> This works fine in SQL Server, but I've also written a UDF which is
> passed A.Z and returns MNO through direct reference to table C
> (SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
> I;m only working on a small DB, so performance is not an issue, but
> I'm curious as to which should have better performance considerations
> so I know which way to take things. The UDF certainly makes things
> tidyier, so I will stick with it unless there's a reason not to.
> Thanks,
> Matt|||Yes, accepts A.Z and B.Z (If A.Z is null, then uses B.Z to get
required value).
I'll try looking at execution plan - forgot about that option. I
gather there are no general rules with this sort of thing, since
execution plan really answers the question definitively.
Thanks,
Matt
On Mon, 31 Jan 2005 07:23:20 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Matt
>Does ther UDF accept a parameter?
>(Untested)
>SELECT .......
>CASE
> WHEN EXISTS (
> SELECT * FROM C WHERE PK = Value)
> THEN C1.Y
> ELSE C2.Y
> END AS MNO
> ...
> FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>
>PS. Have a look at execution plan of yours query?
>You can make it as a stored procedure which will accept a parameter so I
>gave an idea .
>
>"Matt Bolton" <m3it@.technologist.com> wrote in message
> news:8kdrv0tr5l9dlk16vl0dl97hsj959u1rfv@.
4ax.com...
>|||On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:

>I have a query in access which includes the following
>SELECT
> ...
> Case when A.Z is not null then C1.Y else C2.Y END AS MNO
> ...
>FROM (A INNER JOIN
> (B LEFT JOIN C C1 ON B.Z = C1.PK)
> ON A.PK = B.PK)
> LEFT JOIN C C2 ON A.Z = C2.PK;
>This works fine in SQL Server, but I've also written a UDF which is
>passed A.Z and returns MNO through direct reference to table C
>(SELECT @.MNO = Y FROM C WHERE PK = '' + @.ValIn + ''
>I;m only working on a small DB, so performance is not an issue, but
>I'm curious as to which should have better performance considerations
>so I know which way to take things. The UDF certainly makes things
>tidyier, so I will stick with it unless there's a reason not to.
Hi Matt,
I'll come to your question shortly. First another remark.
Are you sure you made no errors when copying the query in your message? It
seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
the description for B.Z from the lookup table if A.Z has a value and will
display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
the rest of the message, I'll assume that you meant it to be the other way
around.
WRT the question: performance will probably be better if you stick to the
current version. The UDF you use is scalar (you don't post the complete
code, but this is what I conclude from what you do post). This means that
it will be called once for each row in the result set. And for each call,
a seperate read in the lookup table has to be performed. For the query
above, the execution plan MIGHT be the same - but it need not. The
optimizer will consider the various indexes available, check some
statistics and then create an execution plan with the best expected
exectution speed. Of course, as Uri indicated, actually testing the
different versions is the best way to know for certasin which will be the
fastest.
You mention "tidyier". This is of course very much based on personal
preferences, so take the following with a grain of salt - but I think that
you could make your current query tidyier without using a UDF, simply by
exchanging the order in which the tables appear in the FROM clause:
SELECT ....
CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
...
FROM A
INNER JOIN B
ON B.PK = A.PK
LEFT JOIN C AS C1
ON C1.PK = A.Z
LEFT JOIN C AS C2
ON C2.PK = B.Z
If you only need the C table for the lookup in the select and don't use
any value from C in the rest of the query, you could also consider the
following alternative (but do test it for performance impact!):
SELECT ....
(SELECT C.Y
FROM C
WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
...
FROM A
INNER JOIN B
ON B.PK = A.PK
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Yes, you're correct about C1 and C2 being swapped. Thanks for the
suggestions - the COALESCE (C1,Y, C2.Y) is simplest and works.
I agree that my join clause is a bit untidy - it's the output of a VB
module I use to make writing complex queries easier - but it needs a
bit of tidying itself.
I'd got the impression from some of the reading I've done here that I
should try to do less calculation in queries and as much as possible
in SP/UDF, and the main thrust of my question was to confirm that -
which you (and others) haven't - so either way I now have a clearer
understanding of where my project's headed.
Thanks,
Matt
On Mon, 31 Jan 2005 09:23:29 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Mon, 31 Jan 2005 15:56:44 +1100, Matt Bolton wrote:
>
>Hi Matt,
>I'll come to your question shortly. First another remark.
>Are you sure you made no errors when copying the query in your message? It
>seems to me that if A.Z is not null, you'd want to select C2.Y, not C1.Y,
>as C2.Y is joined on A.Z and C1.Y on B.Z. The code you posted will display
>the description for B.Z from the lookup table if A.Z has a value and will
>display NULL is A.Z has no value (as C2.Y will be NULL in that case). In
>the rest of the message, I'll assume that you meant it to be the other way
>around.
>WRT the question: performance will probably be better if you stick to the
>current version. The UDF you use is scalar (you don't post the complete
>code, but this is what I conclude from what you do post). This means that
>it will be called once for each row in the result set. And for each call,
>a seperate read in the lookup table has to be performed. For the query
>above, the execution plan MIGHT be the same - but it need not. The
>optimizer will consider the various indexes available, check some
>statistics and then create an execution plan with the best expected
>exectution speed. Of course, as Uri indicated, actually testing the
>different versions is the best way to know for certasin which will be the
>fastest.
>You mention "tidyier". This is of course very much based on personal
>preferences, so take the following with a grain of salt - but I think that
>you could make your current query tidyier without using a UDF, simply by
>exchanging the order in which the tables appear in the FROM clause:
>SELECT ....
> CASE WHEN A.Z IS NOT NULL THEN C1.Y ELSE C2.Y END AS MNO,
>-- or: COALESCE (C1,Y, C2.Y) -- if this can be used depends on your data
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>LEFT JOIN C AS C1
> ON C1.PK = A.Z
>LEFT JOIN C AS C2
> ON C2.PK = B.Z
>If you only need the C table for the lookup in the select and don't use
>any value from C in the rest of the query, you could also consider the
>following alternative (but do test it for performance impact!):
>SELECT ....
> (SELECT C.Y
> FROM C
> WHERE C.PK = COALESCE (A.Z, B.Z)) AS MNO,
> ....
>FROM A
>INNER JOIN B
> ON B.PK = A.PK
>Best, Hugo|||On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
(snip)
>I'd got the impression from some of the reading I've done here that I
>should try to do less calculation in queries and as much as possible
>in SP/UDF,
(snip)
Hi Matt,
In SP: yes. Not because SP will perform better than queries, but for two
other reasons:
1. Security: don't give any user any rights to modify data in any table;
instead, give them only execute permission on stored procedures that will
perform the desired modifications in a controleed manner. Or take it a
step further: don't give any rights to view the data in the tables or
views at all; give them some more stored procedures to get the requested
data.
2. Indirect performance benefit: because you can control the quality of
the SQL in the procedures and fine-tune your indexes to the contents of
these procedures, you can gain performance. You'll never have that level
of control if you allow application programmers or even end users to
create their own ad-hoc queries.
In UDF: no. Especially scalar UDF's are a performance drag, because they
ahve to be executed once for each row. A scalar UDF that reads data from a
table will allways be outperformed by incorporating the logic into the
query. Of course, there may be other reasons that make the UDF the best
choice, but you do take the performance hit.
Table-valued UDF are not nearly as bad. In fact, a query using inline
table-valued UDFs will often result in the same execution plan as an
equivalent query that replaces the UDF by incorporating it's logic into
the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks again Hugo for your assistance.
Matt
On Tue, 01 Feb 2005 00:56:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>On Tue, 01 Feb 2005 10:01:25 +1100, Matt Bolton wrote:
>(snip)
>(snip)
>Hi Matt,
>In SP: yes. Not because SP will perform better than queries, but for two
>other reasons:
>1. Security: don't give any user any rights to modify data in any table;
>instead, give them only execute permission on stored procedures that will
>perform the desired modifications in a controleed manner. Or take it a
>step further: don't give any rights to view the data in the tables or
>views at all; give them some more stored procedures to get the requested
>data.
>2. Indirect performance benefit: because you can control the quality of
>the SQL in the procedures and fine-tune your indexes to the contents of
>these procedures, you can gain performance. You'll never have that level
>of control if you allow application programmers or even end users to
>create their own ad-hoc queries.
>
>In UDF: no. Especially scalar UDF's are a performance drag, because they
>ahve to be executed once for each row. A scalar UDF that reads data from a
>table will allways be outperformed by incorporating the logic into the
>query. Of course, there may be other reasons that make the UDF the best
>choice, but you do take the performance hit.
>Table-valued UDF are not nearly as bad. In fact, a query using inline
>table-valued UDFs will often result in the same execution plan as an
>equivalent query that replaces the UDF by incorporating it's logic into
>the query.
>Best, Hugosql