7

Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to make this decision based on actual performance numbers. I'm also curious how much of a difference there is between the two.

How can I benchmark the performance of these UDFs to figure out which one is faster?

Sandeep Kumar M
  • 4,642
  • 3
  • 32
  • 35
Factor Mystic
  • 241
  • 2
  • 8
  • 'Good/Best' are considered to be subjective questions, that is why I re-framed your question. – Sandeep Kumar M Jan 20 '11 at 09:09
  • In this particular case, its not about the UDF; whether CTE or Cursor causing the performance. You are free to post both UDF's definition here, so that experts can look at it and suggest work arounds. – Sandeep Kumar M Jan 20 '11 at 09:11
  • @Sandy, posting the udf definitions sounds like it would lead to qualitative analysis, which is definitely not what I'm asking for. I want to base this decision on the cold hard truth of raw performance numbers only. – Factor Mystic Jan 20 '11 at 15:35

1 Answers1

7

The SQL Profiler, execution plan, IO/Cpu statistics are your best friend at checking the performance of your code. You will see if there are any differences in performance between the 2 versions of functions.

In this question - Tuning exercises - you can find some basic and advanced references about tuning.

Community
  • 1
Marian
  • 15,531
  • 2
  • 60
  • 74