CLR - Pro SQL Server Internals - page 260

Table 13-2. Invocation overhead of T-SQL and CLR routines: Execution time

CLR UDF
No Data-Access context

CLR UDF
With Data-Access context

T-SQL
Scalar UDF

T-SQL Inline
Multi-Statement

167 ms 246 ms 675 ms 18 ms

Each statement performs a clustered index scan of the dbo.Numbers table and checks if the Num column is even for
every row of the table. For CLR and T-SQL scalar user-defined functions that would introduce the function calls. Inline
multi-statement functions, on the other hand, perform the calculation inline.
As you see, CLR UDF without a data access context runs about four times faster when compared to the T-SQL
scalar function. Even with data access context overhead, the CLR implementation is still faster than T-SQL scalar UDF,
although, in this particular example, the best performance can be achieved if we stop using functions at all rather than
converting them to CLR. The overhead of the function call is much higher than with inline calculations.
While you should always think about code refactoring as an option, there are instances when CLR will
outperform inline T-SQL implementation even with all of the overhead involved. The two most common areas for this
are mathematical calculations and string manipulations.
Let's test the performance of the function that calculates the distance between two points defined by latitude and
longitude coordinates. The CLR implementation is shown in Listing 13-10. The T-SQL implementation is shown in
Listing 13-11. We will test two T-SQL approaches: scalar and inline table-valued functions.

Listing 13-10. Calculating the distance between two points: CLR function
[Microsoft.SqlServer.Server.SqlFunction(
IsDeterministic=true,
IsPrecise=false,
DataAccess=DataAccessKind.None
)]
public static SqlDouble CalcDistanceCLR
(
SqlDouble fromLat, SqlDouble fromLon,
SqlDouble toLat, SqlDouble toLon
)
{
double fromLatR = Math.PI / 180 * fromLat.Value;
double fromLonR = Math.PI / 180 * fromLon.Value;
double toLatR = Math.PI / 180 * toLat.Value;
double toLonR = Math.PI / 180 * toLon.Value;
return new SqlDouble(
2 * Math.Asin(
Math.Sqrt(
Math.Pow(Math.Sin((fromLatR - toLatR) / 2.0),2) +
(
Math.Cos(fromLatR) *
Math.Cos(toLatR) *
Math.Pow(Math.Sin((fromLonR - toLonR) / 2.0),2)
)
)
) * 20001600.0 / Math.PI
);
}