Calling function twice in same select

Question

Calling function twice in same select

Chrissy321

SSCoach

Points: 15763
More actions
December 8, 2010 at 6:27 pm

#99442

Below is pseudo-code which I am using in a View
SELECT
(really complicated code with Case statements) AS NumericalRanking,
(select my alpharanking From rankings where NumericalRanking =
(
(same really complicated code with Case statements as above)
)
AS AlphaRanking
FROM MYTables
I'd like to replace the complicated code with a function.
My questions are:
1) If I place the function in the select twice does the function run twice or does the optimizer 'remember' the result from the first time.
2) If the function does run twice what are my options? I can't use CTE. I could park the function results in a temp table and the join to it but I don't think I can access a temp table in a view.
Any thoughts are welcome. Thanks.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

Let me please amend this by saying I can use CTE since I am working in a 2005 environment but the proposed function would be referencing a linked 2000 database.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Chrissy321 (12/8/2010)
Let me please amend this by saying I can use CTE since I am working in a 2005 environment but the proposed function would be referencing a linked 2000 database.

I caught a surprise today, on this very topic. You cannot call a function directly via a linked server. You can only do it with the two part name, it must be a local database function.

And this is a good thing. Imagine a 2000 row per row function call over a linked server... *shakes off the chills*.

Moving on, your local function can call a linked server if memory serves.

So, with that out of the way. No, your view cannot use a temp table, it needs to be a single statement. It can use cte's on the 2k5 box though. CTE's, however, unless recursioning, are really just nice ways to organize subqueries.

I believe that a function called for a row with the same parameters as other calls is 'remembered', but I'd have to do some testing to confirm that. You can as well, actually, just turn on SET STATISTICS IO ON/OFF, do a query with a single function, and another with the same function called twice, see if the # of reads changes. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 3

Try providing the result from the function as a Cross Apply

Cross Apply(Select Result=dbo.Function(Parameter1,etc)) b

At least the code is cleaner.

LutzM SSC Guru Points: 107049 More actions · Answer 4

If your ReallyComplicatedCode is based on columns of the same row in the same table you could add a computed (persisted) column to the table and use that in your view.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Can you post the whole view definition, Chrissy?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 6

SELECT

b.NumericalRanking,

(select my alpharanking From rankings where NumericalRanking =b.NumericalRanking )

AS AlphaRanking

FROM MYTables a

Cross Apply(Select NumericalRanking =(really complicated code with Case statements) )b

Test with Set Statistics Profile on and confirmed that that Cross Apply is executedonly once

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) Linked servers are often HORRIBLE from a performance standpoint, although there are things you can to do mitigate this.

2) Scalar UDFs can be even WORSE for performance.

I recommend you get a performance tuning professional in for a quick consultation and some mentoring to resolve this immediate issue and teach you some best practices.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

WayneS SSC Guru Points: 95460 More actions · Answer 8

How about this?

SELECT t1.NumericalRanking,

AlphaRanking

FROM (SELECT NumericalRanking = really complicated code FROM MyTables) t1

JOIN rankings r

ON r.NumericalRanking = t1.NumericalRanking;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

Chrissy,

I'm with Kevin on this one - get someone who knows to help you out and learn something from.

If you can't do that then here's some advice:

1. NEVER join tables between the local server and a linked server (unless there are only a few rows of data on the linked server). It's far better to query the linked server for just the rows you need and put them into a temp table or tables. Then you can join to the temp table(s) on the local server.

2. NEVER use a scalar UDF that does any sort of lookup on tables. That is hidden RBAR (Row By Agonizing Row). Any use of a scalar UDF even if it doesn't lookup from tables will throw parallel processing out of your query (Kevin busted my chops on this one recently). This may or may not affect your performace - depending on the query.

3. NEVER use a scalar UDF as a predicate, whether in a WHERE clause or part of a JOIN condition. They aren't SARGable (Search ARGument - able) and the optimizer won't be able to use an appropriate index.

Todd Fifield

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

Craig Farrell (12/8/2010)
I believe that a function called for a row with the same parameters as other calls is 'remembered', but I'd have to do some testing to confirm that.

Nope, 🙂 not even within a case statement as ive demonstrated here...

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/09/06/an-impossible-case.aspx

I agree 100% with the general sentiment...

In 90% ( perhaps 99.99% ) the time scalar functions are not required and only serve to suck performance out of the server.

Clear Sky SQL
My Blog[/url]

Chrissy321 SSCoach Points: 15763 More actions · Answer 11

Edward Boyle-478467 (12/12/2010)
Try providing the result from the function as a Cross Apply
Cross Apply(Select Result=dbo.Function(Parameter1,etc)) b
At least the code is cleaner.

Alas no. Its six table with case and coalesce logic.

Chrissy321 SSCoach Points: 15763 More actions · Answer 12

ChrisM@home (12/12/2010)
Can you post the whole view definition, Chrissy?

No my employer does not allow this which is why I post pseudo code and only hope for for pseudo answers.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

Dave Ballantyne (12/13/2010)
Craig Farrell (12/8/2010)
I believe that a function called for a row with the same parameters as other calls is 'remembered', but I'd have to do some testing to confirm that.
Nope, 🙂 not even within a case statement as ive demonstrated here...
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/09/06/an-impossible-case.aspx
I agree 100% with the general sentiment...
In 90% ( perhaps 99.99% ) the time scalar functions are not required and only serve to suck performance out of the server.

*jawdrop* CLAAAAANNNGGGGGGG.....

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Chrissy321 SSCoach Points: 15763 More actions · Answer 14

Chrissy321 (12/13/2010)
Edward Boyle-478467 (12/12/2010)
Try providing the result from the function as a Cross Apply
Cross Apply(Select Result=dbo.Function(Parameter1,etc)) b
At least the code is cleaner.
Alas no. Its six table with case and coalesce logic.

Sorry this should have been the response to the computed column suggestion.