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.
- 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)
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.