CLR - Pro SQL Server Internals - page 262

select
2 * asin(
sqrt(
power(sin((FromLatR - ToLatR) / 2.), 2) +
(
cos(FromLatR) *
cos(ToLatR) *
power(sin((FromLonR - ToLonR) / 2.0),2)
)
)
) * 20001600. / pi() as Distance
from Rads
);

When we compare the results of the calculations for 262,144 rows, as shown in Table 13-3 , we see that CLR UDF
performs almost two times faster compared to the inline table-valued function and more than five times faster when
compared to T-SQL scalar UDF.

Table 13-3. Calculating distance between two points: Execution time

CLR UDF TSQL Scalar UDF TSQL Inline Table-Valued function

347 ms 1,955 ms 721 ms

Now let's look at data access performance. The first test compares the execution time of the separate DML
statements from the T-SQL and CLR stored procedures. In this test, we create the procedures that calculate the
number of the rows in the dbo.Numbers table for a specific range of numbers. The T-SQL and CLR implementations
are shown in Listings 13-12 and 13-13 respectively.

Listing 13-12. Data access performance: T-SQL procedure (individual statements)

create proc dbo.ExistInInterval
(
@MinNum int
,@MaxNum int
,@RowCount int output
)
as
set nocount on
set @RowCount = 0
while @MinNum <= @MaxNum
begin
if exists
(
select *
from dbo.Numbers
where Num = @MinNum
)