Function Takes time

Question

Hi, All

     I have a function call from several SP. When i run this Function from t-sql it returns values in 2 to 3 second, and  i have optimized this function to return value in 1 sec by adding an index. but when it calls from another SP and try to use  set @variable = function, it takes about 7 minutes and hold up all the process.

 any suggestion.

Answer 1

check the execution plan of store procedure, it will give you picture that which batch of quaries in SP is taking how much cost %.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

it says 4 % only.

Answer 3

Post the code for the function.

A scalar valued function is slow because the exec plan for the function is "not fully incorporated" into the outer query exec plan.
What you should try to do, is to rewrite the function as an inline table valued function.

---

N 56°04'39.26"
E 12°55'05.63"

Answer 4

Could be parameter sniffing. Could be something else. This article on my web site may help you:
http://www.sommarskog.se/query-plan-mysteries.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

UDFs cannot be optimized so good SQL programmers do not use them.  However, BASIC programmers still think that way and even put the old "fn_" prefix on them! Since it is very hard to read your mind, why don't you post the code and DDL so we can turn it from 1960's BASIC into 2012 SQL?

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

UDFs cannot be optimized so good SQL programmers do not use them.

As usual Joe Celko shows that he does not know what he is talking about. (And I doubt that he would qualify as good SQL Server programmer.)

A scalar function or a multi-statement function is a suite of SQL statements, and these statements can be optimized like any other SQL statements. What is problematic is they appear in an query, because the optimizer cannot unfold the UDF into the outer query. For this reason, you should be very careful with scalar UDFs that perform data access; this can be very costly.

There are also inline functions, and they are just like parameterised view and come with no overhead at all; SQL Server optimizes the expanded query. Unfortunately there are only inline table functions, not inline scalar functions.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Erland-

That's just too funny!!  Keep 'em coming!!

---

Ryan Shuell