|
Recent
Articles |
MySQL Gets Telecom-Specific
Due to demand, MySQL AB announced the expansion of its
telecom-specific services and consulting for MySQL software.
Specifically, the company is promoting its MySQL Cluster,
a high-end version of the standard database designed for
fault-tolerant, mission-critical network and telecommunications
applications at...
Practice
Makes Perfect For SQL
MySQL guru Sheeri Kritzer listed eight SQL best practices
for database professionals who are hard at work on...
Ingres
Christens Project Icebreaker
The open source database company Ingres teamed with another open source player
to deliver Icebreaker, a way to place database services on a server with no operating
system required. Ingres CTO Dave Dargo blogged about Icebreaker...
MySQL
Turbo Manager And Dream Coder
The success and the longevity of any prominent business
nowadays depend on powerful application infrastructures
and effective, reliable management...
SQL
Replication
Database management systems are very important for business
today. Whether your application has a network access to
all employees in your organization or...
|
|
 |
Recent
WebProNews Articles |
Google Seething Over Belgian Judgment
Being compelled to remove certain news sources from the
Google index and Google News was not a big deal for the
company, but being required to post the judgment on its
Belgian homepage apparently touched a nerve.
MySpace, Seventeen Join Up For Teen Safety
MySpace is partnering with Seventeen magazine, the National School Board Association and the National Association of Independent Schools to launch a new safety campaign aimed at warning and educating teenagers about the dangers of the Internet.
Microsoft Readies Its Google Killer
During the Advertising Week 2006 conference, Microsoft will debut the global launch of its digital advertising platform, which will encompass platforms from the PC to mobile devices to the Xbox gaming system.
Search Shows Your Political Thoughts
The queries people make at search engines demonstrate what is on the minds of the electorate, as the latest data from Hitwise indicated. Abortion, war, and illegal immigration were among the issues U.S. searchers looked for in queries for the twelve weeks leading up to September 16th.
Microsoft Works To Battle Google
Microsoft may set up an online version of its low-end Works productivity suite to compete with offerings from Google and a number of startup companies.
|
|
 |
|
09.26.06
CLR Vs T-SQL And Stored Procedures In SQL Server 2005
 By
Dmitry Tsuranoff
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 Settings\User\My Documents\Visual Studio 2005\Projects\SQL2005_Assembly\bin\Release\SQL2005_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.
About the Author:
http://www.lakesidesql.com/
|
