December 14, 2018 at 9:22 am
I've been trying to improve the performance of a rather sluggish stored procedure, which has a fairly complex udf within it.
The udf just shows as one box on the query plan and I get nothing from setting STATS IO ON, to provide any clues as to it's performance.
This is of course a widely documented feature of udfs.
So, I've tried to replace it with a single statement tvf, which shows some promise, because I now have an execution plan with useful information and STATS IO ON actually tells me things.
However, it also takes about three times longer to execute - so my victory is a hollow one.
Part of the reason might be because I resorted to using COALESCE as the main part of the query.
To explain - the udf performed one of three queries, depending upon the result of each:
<SELECT @1 FROM Table1>
If @1 IS NULL
....SELECT @2 FROM....
....IF @2 IS NULL SELECT @3 FROM....
ELSE
....SELECT @4 FROM .....
And the queries that select @2, @3 or @4 aren't pretty.
So, after some tidying, the main solution was to use COALESCE:
SELECT COALESCE((SELECT @4 FROM...), (SELECT @2 FROM...),(SELECT @3 FROM...)) AS T1
It works, which quite surprised me but it doesn't execute well.
Having looked at the documentation for COALESCE it looks like each of these SELECT statements will execute multiple times, which isn't what I thought. I had assumed it would try the first SELECT and if that was NULL then proceed to the next and so on. Not so.
I'm trying to find a solution that will do such a thing but trying 'IF ISNULL(' gives me a syntax error - as it doesn't appear to like it immediately after the RETURN within the tvf.
December 14, 2018 at 1:11 pm
Extremely vague. But in general, rather than actually SELECTing the data, using EXISTS() to check for it is more efficient.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 15, 2018 at 5:06 am
We need lot more information here, no way of helping without is.
😎
Having no code, execution plan or anything substantial, my guess would be "Full Moon"😉
December 17, 2018 at 4:26 am
Yeah, I need to put together a test project to demonstrate this. I can't post the code because it is a central function for the connection security, for one of our main systems. It wouldn't look good to provide little clues on what it is after.
As an aside - Brent posted a link today that might be related to what I'm seeing. I always thought COALESCE and CASE short-circuited when they found a condition (in sequence) that was satisfied. Not so, on some occasions.
I'll have a play over the next couple of days and try to come up with a demo that doesn't mean I need to prepare three envelopes.
Thanks for your time.
(Edited to correct typo)
December 17, 2018 at 5:30 am
Try this, Steve. I've done something similar before:
SELECT MySecurityToken = COALESCE(d.c1, x1.c2, x2.c3, x3.c4)
FROM (SELECT c1 = MAX(something) FROM Table1) d
OUTER APPLY (SELECT c2 = MAX(something) FROM Table2 WHERE d.c1 IS NULL) x1
OUTER APPLY (SELECT c3 = MAX(something) FROM Table3 WHERE x1.c2 IS NULL) x2
OUTER APPLY (SELECT c4 = MAX(something) FROM Table4 WHERE x2.c3 IS NULL) x3
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply