Performance - View vs. SP

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

  • 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

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

  • 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

  • tinausa - Thursday, September 7, 2017 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.

    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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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