SQLTeam.com | Weblogs | Forums

SQL Table Valued function performance

sql2012

#1

I have one table valued function which is running very slow where as if i take the query from inside the function and run separately then query runs very fast.

Function structure:

  • Three CTEs are used in function
  • output of the CTE is inserted into Table variable and which is returned from the function.

If i take out the query from the function and run separately by passing the same parameter which i am passing in function, the query executes in 40 seconds where as function is taking 2 hours 30 minutes to execute.

Please let me know if any optimization i can do on functions to improve the performance, even Print and Raiserror is not allowed within the function so couldn't check that which step is taking time and i don't have access on SQL profiler.

I executed below query and captured the attached execution plan:

SELECT * FROM Function(@Parameters)

execution%20plan

How to view actual execution plan for table valued function,
Above execution plan only showed one object for function but i want to see detail plan for the function.

Thank you in advance and i can't post the code due to company policy, i know without looking code it is hard to provide solution but if you could provide me some of the basic/advance debugging methods so i can try to figure out what is wrong with the function.


#2

i hope the following link helps

i have posted screenshots of options that might work for you

:slight_smile:
:slight_smile:

`


#3

Hi Harish,
Thank you for the snapshots.
I have captured the execution plan using method 1 but as per the snapshot i attached when i executed the function in SELECT query i only got one object for TVF, please refer to snapshot:


#4

hi

one thing i know is ..

create function
code

remove the create function part

put only the code in ssms

and see execution plan


#5

40 seconds is an internity.
Then you use in a select statement with 1000s of rows it will come to bite you.
CTEs and table variables are poor for performance with large data sets.
We need to see your tvf. You are asking us to cook you a 3 course meal with no recipe and no utensils and no oven


#6

The problem you are running into is typical when utilizing a multi-statement table-valued function. You may be able to improve performance by converting to an inline table-valued function and then CROSS/OUTER APPLYing the function...

The usage of CTEs will only be a problem if they are set as recursive CTEs - if that is not the case then they are no different from using derived tables in your query. Verify that you are not utilizing the CTEs as recursive and that your code is as efficient as possible...

If you want help on optimizing that code - you need to share the code with some sample data...