3

i have a table valued function with quite some code inside, doing multiple join selects and calling sub-functions and returns a result set. during the development of this function, at some point, i faced a performance degradation when executing the function. normally it shouldn't take more than 1 sec but it started taking about 10 sec. I played a bit with joins and also indexes but nothing changed dramatically. after some time of changes and research, I wanted to see the results with another way. I created the same exact code with same exact parameters as a stored procedure. then i executed the sp. boom! it takes less then 1 sec. the same exact code takes about 10 sec with a function.

i really cannot figure out what this all about and i have no time to do more research. I need it as a function for some reasons but i don't know what to do at this point. I thought i could create it as a proc then call it within the function but then i realized it's not possible to do it for functions.

i wanted to hear some good views and advice here from experts. thanks in advance

ps:i did not add any code here as the code is not in a good format and quite dirty. i would share it if anybody is interested. server is sql 2014 enterprise 64 bit edit: i saw the possible duplicate question before but it did not satisfy me as my question is specifically about performance hit. the other question has many answers about general differences between procedures and functions. i want to make it more clear about possible performance related differences.

Mehmet Hulusi Akyıl
  • 31
  • 1
  • 3
  • 1
    Stored procedure caches the execution plan and the user defined function doesn't, hence better performance with the stored procedure. Google `SQL Server Stored procedure execution plan` and you will learn a lot. – M.Ali Jul 02 '15 at 23:34
  • possible duplicate of [Functions vs Stored Procedures](http://stackoverflow.com/questions/178128/functions-vs-stored-procedures) – Brent D Jul 03 '15 at 00:33
  • As @M.Ali mentioned, multi-statement table functions do not store the execution plan, whereas Stored Procedures do. Check out the links & discussion topics in [Functions vs Stored Procedures](http://stackoverflow.com/questions/178128/functions-vs-stored-procedures) – Brent D Jul 03 '15 at 00:35
  • To perform well, you'll need to refactor as an inline table-valued function but that doesn't seem like an option based on your description. In-line TVFs can be expanded and optimized but not multi-statements ones, Also, they don't have statistics so the query optimizer will have a hard time coming up with an good plan for larger data sets. – Dan Guzman Jul 03 '15 at 01:10

1 Answers1

2

These are the differences from my experience:

  • When you first started writing the function, you are likely to run it with the same parameters again & again until it works correctly. This enables page caching in which SQL Server keeps the relevant data in memory.
  • Functions do not cache their execution plans. As you add more data, it takes longer to come up with a plan. SET STATISTICS TIME ON to see query compilation time vs. execution time.
  • Functions can only use table variables and there's no stats on those. That can make for some horrendous JOIN decisions later.

Some people prefer table-valued functions because they are easier to query:

SELECT * FROM fcn_myfunc(...) WHERE <some_conditions>

Instead of creating a temp table, exec the stored procedure then filter off that temp table. If your code is performance critical, turn it into a stored procedure.

Code Different
  • 90,614
  • 16
  • 144
  • 163
  • SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (5 row(s) affected) SQL Server Execution Times: CPU time = 12531 ms, elapsed time = 13106 ms. does this make any sense? – Mehmet Hulusi Akyıl Jul 03 '15 at 08:16