Performance impact of a group by function call

Question

I have the following query, which for simplicity sake I use the simple example format below:

SELECT 
  col1, col2, dbo.fun3(col3)
GROUP BY col1, col2, dbo.fun3(col3)

This took 30 seconds to run and returned 6000 records. When I removed dbo.func3(col3) in both the SELECT and GROUP BY it sped up to only 5 seconds to return 6000 records.

So I guess my performance impact comes down to the following:

• dbo.func3() is slow and need further work to improve its performance
• The function call in the GROUP BY causes the slowness.

I wanted to isolate how slow is dbo.func3() is so I ran the following query without GROUP BY:

SELECT col1, col2, col3

versus

SELECT col1, col2, dbo.fun3(col3)

The first one returns around 900,000 records in 15 seconds. The second one return 500,000 records in 15 seconds.

This prove I should improve my func3() function. However, do you know of anything I can do in the group by part of the query to reduce the delays?

Is this function being called twice because I have it in the select and the group by portion of my query?

Answer 1

So I guess my performance impact comes down to the following

Since your question is about performance I advise you to start using performance tools that you already have at your disposal to dig into this problem. This way your guess will be more precise or better yet: you won't have to guess at all.

Display an Actual Execution Plan

SET STATISTICS TIME (Transact-SQL)

SET STATISTICS IO (Transact-SQL)

If after using these tools you are not able to figure out your answer, update your questions adding their result plus the function code and we'll try to help you further.

However, do you know if anything I can do in the group by part of the query to reduce the delays?

Without your actual code it's not possible to help very much.

Is this function being called twice because I have it in the select and the group by portion of my query?

It can be verified on the execuion plan.