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?
Best Answer
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.
Without your actual code it's not possible to help very much.
It can be verified on the execuion plan.