CLR - Pro SQL Server Internals - page 263

set @RowCount += 1
set @MinNum += 1
end

Listing 13-13. Data access performance: CLR procedure (individual statements)
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ExistInIntervalCLR
(
SqlInt32 minNum,
SqlInt32 maxNum,
out SqlInt32 rowCnt
)
{
int result = 0;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
@"select Num
from dbo.Numbers
where Num between @minNum and @maxNum", conn);

cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@Number", SqlDbType.Int);
for (int i = minNum.Value; i <= maxNum.Value; i++)
{
cmd.Parameters[1].Value = i;
cmd.ExecuteNonQuery();
result += (int)cmd.Parameters[0].Value;
System.Threading.Thread.Sleep(0);
}
}
rowCnt = new SqlInt32(result);
}

Table 13-4 shows the average execution time for stored procedure calls that lead to 50,000 individual SELECT
statements. As you see, data access using CLR code works about five times slower than data access using T-SQL.

Table 13-4. Data access performance (individual statements): Execution time

T-SQL Stored Procedure CLR Stored Procedure

410 ms 2,330 ms

You need to keep this in mind when designing user-defined functions that need to access data from the database.
While CLR is more efficient than T-SQL in terms of invocation, data access code will work significantly slower. You
need to test both implementations to figure out which solution is more efficient for your purposes. Moreover, you
need to consider code refactoring and removing UDF from the queries as another possibility.
In the next step, let's look at the performance of the .Net SqlDataReader class and compare it to cursor
implementation in T-SQL. You can see the CLR code in Listing 13-14 and the T-SQL implementation in Listing 13-15.