Sql-server – How to benchmark the performance of UDFs to figure out which one is faster

benchmarkperformancesql-server-2008

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?

Best Answer

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.

Related Solutions

Sql-server – Why the query scan all the partitions

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.

Postgresql – How to figure out what planner cost constants to use in Postgres

First, the costs are not supposed to directly relate to the execution time. They're strictly relative; a plan which costs more should take longer to actually execute. You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.

For 99% of users, there's only three cost constants they care about at all:

effective_cache_size: set this to 75% of available RAM on your system. Done.

random_page_cost: if you're using high-end SAN/DAS, lower this to 2.0. If you're using SSDs or on Amazon, lower it to 1.5. Otherwise, leave it alone.

effective_io_concurrency: if you're on Linux and have a drive array, raise this to the number of drives (or mirrored pairs) the database is stored on, maximum 4.

Tinkering with the other cost constants is not recommended unless you have a full performance test setup to determine if the result is really beneficial or not.

Best Answer

Related Solutions

Sql-server – Why the query scan all the partitions

Postgresql – How to figure out what planner cost constants to use in Postgres

Related Question