Looping between a big number of records is a CPU-intensive operation that can have a significant impact on your database performance. I've talked in the past that with SQL Server 2005 you can use SQL CLR (.NET) for improve your performances on CPU-intensive operations, such as looping, iterations, calculations etc.
But is it always true that for these types of operations SQL CLR is the best choice?
I've tryed a curious test by writing a simple T-SQL stored procedure that makes a big loop and the same I've done via a SQL CLR stored procedure.
The T-SQL code is this:
create proc sp_Loop
as
declare @i int;
set @i=0;
while @i<5000000
begin
set @i = @i+1
end
go
while the SQL CLR Stored procedure is this:
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void sp_DotNetLoop()
{
int i=0;
while(i<5000000) {
i++;
}
}
};
Now try to execute the T-SQL stored procedure with this code:
declare @start datetime, @end datetime
set @start=getdate()
exec sp_Loop
set @end=getdate()
print DATEPART(Ms, @end-@start)
The result is that the T-SQL loop is completed in about 480 milliseconds, while the same SQL CLR loop is completed in about 2 milliseconds. Here the SQL CLR is terribly more performant!
The difference appears if you place a code inside the loop that makes a data access or a data output. If you rewrite your T-SQL Stored procedure like this:
create proc sp_Loop
as
declare @i int;
set @i=0;
while @i<5000000
begin
set @i = @i+1
print @i --new line added
end
go
and you do the same also for the SQL CLR procedure (for example by adding a SqlContext.Pipe.Send(i.ToString()) instruction, you can see that now the T-SQL procedure performs better than the SQL CLR ones (around 1500ms vs 2200ms).
So, why this?
SQL CLR has a lost on performance when it has to stream the results back to the database engine, so be careful when you want to extremely tune-up your procedure's performances: what you have to do inside the loop will determine your choice.
Technorati Tag:
.NET,
SQL Server