September 25, 2015 at 1:41 pm
Greetings, all.
I am investigating the performance of several "expensive" functions, exploring options such as refactoring, indexes, etc. One option I am exploring, is converting these functions to stored procedures. I am performing comparative tests of these udf's & their counterpart stored procedures. My test scripts include 5 calls to a given function, using different parameter sets, each call OPTION (RECOMPILE), along with 5 comparable calls to the stored procedure counterpart of the udf, each WITH RECOMPILE.
I am using Management Studio, actual execution plan & client statistics options, as well as SQL Sentry Plan Explorer.
I am confused by what I am seeing.
1. If a examine "total batch cost" in SSMS, or the total subtree costs in SQL Sentry Plan Explorer, the procedure approach wins hands down; the udf typically "costs" 80-90% of the total batch.
2. If I examine the # of key lookups, hash matches, index scans, etc., reported by SQL Sentry Plan Explorer, the stored procedure components have few, while the udf has very many. Only in the "Duration" category does the stored procedure perform worse.
3. If I simply examine the execution time, running them in Management Studio, the stored procedure variant takes longer.
4. Examining client statistics using Management Studio, the stored procedure takes much more execution time & much more client processing time, although it requires dramatically less wait time.
So, I am not really sure what to make of this. Are there particular data items that should be weighted most heavily in this comparison? Is the stored procedure getting treated more kindly by the execution plan & SQL Sentry Plan Explorer than it should be? Just trying to determine which is the better option, as I said, it's a complex set of queries in the function & trying to improve it.
Thanks,
Randy
September 25, 2015 at 2:37 pm
Remove the with and option recompile.
Start with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.
Then, do your 5 iterations of each. See what happens with the execution times.
By doing with recompile, you are starting from scratch each time.
That being said, the nature of the proc/function may be making a difference. Do the procedures fall into the "catch-all" category?
Read these links.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
If the query you are trying to run falls into anything described by Gail Shaw, then the execution times may not increase. The original plan cache may be less than optimal for the subsequent runs of the procedure.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 26, 2015 at 8:16 am
Actually, I want to recompile each time, b/c I want to test each instance of my code w/o it using a cached plan, to check for variances in generated query plans & performance. This is as per testing advice given by Kimberly Tripp in her performance optimization courses. Gail Shaw does refer to this.
I don't think it would fit in the "catch-all" queries described by Gail Shaw. I don't use dynamic SQL, none of the conditions permit NULL values, I'm not using IF control flow variations in the code. I'm testing different input parameter combinations that serve as WHERE or JOIN filters, in which all parameters are required, but some will result in greater numbers of output rows. We're talking typically two or max three parameters here. The queries do involve many joins, with given parameters resulting in greater or lesser row counts.
I haven't tried breaking the code into sub-procedures, one of Gail Shaw's recommendations. But, essentially, the difference in composition is that in the function, everything is one big query composed of various CTE's or subqueries, while in the stored procedure, I am positing result sets in table variables & processing smaller queries in stages.
The optimizer seems to evaluate the procedure approach as superior, with all examinable aspects of the udf being evaluated as more costly, while actual performance in SSMS (cpu, duration, execution time) seems to indicate the reverse. That's what's confusing me.
Thanks,
Randy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply