|
SQLProNews |
|
SecurityProNews |
|
ITmanagement |
|
|
|
|
|
CLR Vs T-SQL And Stored Procedures In SQL Server 2005
By Dmitry Tsuranoff
Expert Author
Article Date: 2006-09-26
CLR or TSQL? That is the question.
More are more developers are struggling to find the right answer, but the answer really depends on the developer's individual needs.
For classic SQL tasks, the good old TSQL is recommended. On the other hand, CLR works best for calculations, parsing, image processing and other tasks that deal with a very limited amount of data.
We performed an experiment that defies the common perception that calculation tasks run several times faster when implemented in CLR form. For this experiment, we utilized a computer with Pentium 4 2.4GHZ processor and 1 Gb of RAM.
We created a very simple user defined function on T-SQL that adds two numbers. The function is found below:
CREATE FUNCTION FuncSum(@n1 INT, @n2 INT) RETURNS INT AS BEGIN RETURN @n1 + @n2 END
Then, we rewrote the same function using CLR this time. This looks a little bit more complicated.
using System; using Microsoft.SqlServer.Server;
public class TestProcedures { [Microsoft.SqlServer.Server.SqlFunction( IsDeterministic = true, DataAccess = DataAccessKind.Read )] [CLSCompliant( false )] public static int FuncSum_CLR(int n1, int n2) { return n1 + n2; } }
After which, we loaded them into our database:
CREATE ASSEMBLY SQL2005_Assembly FROM 'C:Documents and SettingsUserMy DocumentsVisual Studio 2005ProjectsSQL2005_AssemblybinReleaseSQL2005_Assembly.dll' WITH PERMISSION_SET = UNSAFE
CREATE FUNCTION FuncSum_CLR ( @n1 int, @n2 int ) RETURNS int AS EXTERNAL NAME SQL2005_Assembly.TestProcedures.FuncSum_CLR
We then ran the TSQL and CLR function 100,000 times over and compared the result:
CREATE PROCEDURE TestFuncSum(_CLR) AS BEGIN DECLARE @t datetime SET @t = getdate() DECLARE @n INT DECLARE @i INT SET @i = 0 WHILE (@i < 100000) BEGIN EXEC @n = FuncSum(_CLR) 1,1 SET @i = @i + 1 END SELECT datediff(ms, @t, getdate()) END
The results were revealing:
* TSQL: 2000ms * CLR: 6300ms
The experiment proved that CLR was running more than three times slower on a mere calculation task. Here's the explanation:
Obviously, it takes time for the SQL server to switch the context from the kernel to the CLR (.Net framework). This time in that experiment can be estimated using formula (6300-2000)/100000 = 0.042ms. It is almost nothing in comparison with other executions, remember, even SQL profiler does not detect time periods less then 13-16ms.
However, it might be important if you are using CLR functions in WHERE conditions or as a parameters to the aggregation functions, which are called thousands of times.
This overhead is the same for TSQL and CLR procedures.
The Mystery of 'Return'. Why SQL2005 is slower then SQL2000.
We continued on with our experiments that call almost empty procedures thousands of times over. The results are once again revealing.
Experiments are again done on a computer with Pentium 4 2.4GHz processor and 1Gb of RAM.
We created three procedures that do nothing. You read right, they do absolutely nothing.
CREATE PROCEDURE Proc_Return1 AS RETURN GO
CREATE PROCEDURE Proc_Return2 AS RETURN DECLARE @i INT SET @i = 0 GO
CREATE PROCEDURE Proc_Return3 AS RETURN 1 GO
In the second procedure, there were unreachable statements after the RETURN. They were not an error. We too thought that there is no difference, but we then called all three procedures 100K times:
CREATE PROCEDURE TestProc_Return1 AS BEGIN DECLARE @t datetime SET @t = getdate() DECLARE @i INT SET @i = 0 WHILE (@i < 100000) BEGIN EXEC Proc_Return(1/2/3) SET @i = @i + 1 END SELECT datediff(ms, @t, getdate()) END GO
And we got the following results:
SQL 2000 SQL 2005 EXEC TestProc_Return1 2120ms 1850 EXEC TestProc_Return2 1200ms 1850 EXEC TestProc_Return3 1300ms 1850
What do these results mean?
* Mysteriously, unreachable code after the RETURN helps it running faster. Only God and Microsoft know why, I don't have any logical explanation. It is believed that SET statements are more useful then the others. You can continue my experiments with the different statements and share the result.
* RETURN 1 also helps, and is twice faster than RETURN
* Finally, this mysterious behavior is fixed on SQL 2005... it is always running slow...
Now that is more important. Note: this is actually a case where SQL 2005 is running almost 2 times slower then SQL 2000 on the same code. And that can hurt on the production! Is it the only case? No, we will continue the investigation.
Tag: CLR, T-SQL
Add to Del.icio.us | Digg | Yahoo! My Web | Furl
Bookmark WebProNews:
About the Author:
www.lakesidesql.com/
|
|