September 7, 2017 at 1:24 pm
I have inherited a DB where a specific report is created using a view, which in turn calls a function that takes month as a parameter and the function has to be called as many times as the number of months YTD. So 7 times for July.
To do some post-processing to update it, I created a stored proc and put the output of the view into a table and then do some updates on that table.
The old process would take over three hours to run for 6-7 months. The SP on the other hand, takes a few minutes to execute.
I was wondering why such a discrepancy?
Thanks.
September 7, 2017 at 3:43 pm
Find a copy of "SQL Server MVP Deep Dives 2" (which was created and sold as a charity benefit) and read my chapter entitled "Death by UDF". There are umpteen ways UDFs (other than Inline Table Valued Functions) can TOTALLY screw performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 8, 2017 at 5:21 am
I will look at the book and I get that.
But I am still using the function.
Before - the function was being called from a view.
After - the function is being called from a view, which is being called from a stored proc.
Why should that change performance from over 3 hours to less than 30 minutes?
Thanks.
September 8, 2017 at 5:37 am
Look at the execution plans from the two different approaches. That should tell you what's going on with SQL Server.
Without seeing the queries and the plans, I can think of lots of reasons why it might be different, but they'd all be guesses. If you're stuck, post that information here and someone will help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2017 at 8:16 am
tinausa - Thursday, September 7, 2017 1:24 PMI have inherited a DB where a specific report is created using a view, which in turn calls a function that takes month as a parameter and the function has to be called as many times as the number of months YTD. So 7 times for July.To do some post-processing to update it, I created a stored proc and put the output of the view into a table and then do some updates on that table.
The old process would take over three hours to run for 6-7 months. The SP on the other hand, takes a few minutes to execute.
I was wondering why such a discrepancy?
Thanks.
I added some emphasis on the difference. When you were simply using the view, you had to run the view definition every time. Once it's on a table, you stop using the query in the view and work directly with data.
If you post the definitions of the view and the function, along with some sample data (not real data and not even real column names), we might even be able to help you get the process to run in seconds instead of minutes. Take a look on the following article to know what to post: How to Post Performance Problems - SQLServerCentral
September 8, 2017 at 12:40 pm
Thanks Luis, that makes sense.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply