SQL Server Performance Forum – Threads Archive

UDF Performance

Hi, I have a number of UDFs being called in a WHERE clause, but performance seems to be poor. On doing a Profile and Trace, there are a huge number of READs being performed whenever accessing a temporary table used in the UDF, e.g. "insert into @InnerTab select * from GetInner(@a, @b, @c, @d)" results in a READ value of 119. @InnerTab is defined as declare @InnerTab Table
(
inner_num int,
inner_denom int
) GetInner is defined as : CREATE FUNCTION GetInner(@a int, @b int, @c int, @d int)
RETURNS
@InnerTab Table
(
num int,
denom int
)
AS
BEGIN insert @InnerTab
(num, denom)
values
(@a + @b, @c + @d) return END So you can see that not much is happening, just some variables being moved around. There are several UDFs being called for each record, so you can see that it adds up. In fact, for a recordset of only 16 values, there are about 65,000 READs. Is there anyway to improve the performance of this, e.g. can I tell SQL Server to use memory caching rather than going out to disk? Thanks for your help,
Duncan

---

Why the hell did you make it table function?!? Why don’t use scalar function like:
insert into @InnerTab values(dbo.inner(@a, @b, @c, @d))
Your table function returns one row anyway. Even better, you can use:
insert into @InnerTab values(@[email protected], @[email protected])

---

Hi Duncan even i was surprised by the q u posted.
Like mmarovic, even i thought at a first glance that
why u have not used
insert into @InnerTab values(@[email protected], @[email protected]).

---

The Reads columns in Profiler is logical reads, not physical,
i would not worry about the Reads value, and pay more attention to CPU and Duration averaged over many calls,
I would do as much as reasonably practical to avoid table write operations

---

Thanks for the quick responses. The reason I’ve made it a table function is because I’m really a software engineer at heart, and it’s second nature to modularise code into readable chunks rather than having it inline. This is why I have created functions such as GetInner, GetOuter etc to return these values – so they can be reused. These were also simple examples – there are much more complex functions which call other embedded functions etc before they return. I was not expecting the performance to be so poor, so I may bring certain function calls inline as you have suggested. Interesting about the Reads column – couldn’t find where that is defined in BOL. I presume Duration is in milliseconds and CPU is clock cycles?

---

Ok, you can modularise your code by using scalar function instead of table one. That is the part that kills your performance the most IMO. Table functions should be used when you need encapsulate dataset related rules, which means when you need to return more then one row based on set of parameters.

---

Thanks, I’ve just done that and I’ve already seen performance increase by 100%. It’s still too slow, but if I apply the same principles to the rest of my UDFs then it should hopefully give acceptable results. Didn’t realise using Tables would be so slow! Cheers,
Duncan

---

It is slow when you use them for the purpose they are not designed for. However they are ok when used properly. Please give us feedback about performance after you make all changes.

---

As a software engineer working in SQL Server, you should free your mind from the procedural approach to retrieving and manipulating data. Agreed, modularising code into chunks is the way to go in complex procedure-based applications, but in a data-based application like SQL Server you will find that – as a rule – set-based coding performs better. You will be amazed at what you can do with expressions in the standard T-SQL query statements (like SELECT, INSERT, UPDATE …). Also, I’ve seen many programmers creating stored procedures that call other procedures, only to step through a cursor and call another procedure that queries another table for a reference value – which is the same as doing a SELECT on couple of JOINed tables, only with very poor performance. There is a downside in that you will lack the reusable objects that a procedure-based application provides – though there is a lot you can do with VIEWs.

---

I believe that the power is there, you’re right it’s just a different approach to get the best out of it. >> Ok, you can modularise your code by using scalar function instead of table one I have a question on scalar functions – did you mean that you can have a function return multiple values? This is why I was using table functions, thought that was the only way of doing this. Could you give a quick example code of the syntax of this? Can’t find an example anywhere unfortunately!

---

Oh, you are right, I wrote nonsense. [<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />] No, you can’t return multiple values using functions as far as I know. You can return multiple output parameters from stored procedure, but (I agree with Adriaan) executing too much store procedures from other stored procedures doesn’t make sql code more managable and often makes it less efficient.

---

Well mmarovic, actually you can return multiple values using functions – at least if the function returns a table … [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]

---

That’s fine – thanks for the help anyway! I’m going to cache some values in the table to reduce the amount of UDFs that need to be called in the query each time. I’ve also done as much work inline as possible. This should improve performance significantly. I’ll post my results of the performance improvement when I’ve got it working. Thanks for your time, much appreciated!
Duncan

---

Wrong again. I meant from scalar function <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

]]>