Database Reference
In-Depth Information
Listing 13-14.
Data access performance: CLR procedure (SQL Reader)
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ExistInIntervalReaderCLR
(
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("@MinNum", SqlDbType.Int).Value = minNum;
cmd.Parameters.Add("@MaxNum", SqlDbType.Int).Value = maxNum;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result++;
-- Yielding every 500 rows
if (result % 500 == 0)
System.Threading.Thread.Sleep(0);
}
}
}
rowCnt = new SqlInt32(result);
}
Listing 13-15.
Data access performance: T-SQL procedure (Cursor)
create proc dbo.ExistInIntervalCursor
(
@MinNum int
,@MaxNum int
,@RowCount int output
)
as
set nocount on
declare
@Num int
declare
curWork cursor fast_forward
for
select Num
