CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlclrsqlclr
David Pfeffer
David Pfeffer

Reputation: 39833

SQL Server 2008 CLR vs T-SQL: Is there an efficiency/speed difference?

I'm a C# developer who has done some basic database work in T-SQL. However, I need to write a very complicated stored procedure, well above my T-SQL knowledge.

Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL? Is the difference (if any) significant? Why?

Upvotes: 5

Views: 3409

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

The question of "Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL?" is really too broad to be given a meaningful answer. Efficiency varies greatly depending on not just what types of operations are you doing, but also how you go about those operations. You could have a CLR Stored Procedure that should out-perform an equivalent T-SQL Proc but actually performs worse due to poor coding, and vice versa.

Given the general nature of the question, I can say that "in general", things that can be done in T-SQL (without too much complexity) likely should be done in T-SQL. One possible exception might be for TVFs since the CLR API has a very interesting option to stream the results back (I wrote an article for SQL Server Central--free registration required--about STVFs). But there is no way to know for certain without having both CLR and T-SQL versions of the code and testing both with Production-level data (even poorly written code will typically perform well enough with 10k rows or less).

So the real question here boils down to:

I know C# better than I know T-SQL. What should I do?

And in this case it would be best to simply ask how to tackle this particular task in T-SQL. It could very well be that there are non-complex solutions that you just don't happen to know about yet, but would be able to understand upon learning about the feature / technique / etc. And you can still code an equivalent solution in SQLCLR and compare the performance between them. But if there is no satisfactory answer for handling it in T-SQL, then do it in SQLCLR.

That being said, I did do a study 3 years ago regarding SQLCLR vs T-SQL Performance and published the results on Simple-Talk.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425341

CLR require some communication overhead (to pass data between the CLR and SQL Server)

Rule of thumb is:

  • If your logic mostly includes transformations of massive sets of data, which can be performed using set operations, then use TSQL.

  • If your logic mostly includes complex computations of relatively small amounts of data, use CLR.

With set operations much more can be done than it seems. If you post your requirements here, probably we'll be able to help.

Upvotes: 8

Andrew Hare
Andrew Hare

Reputation: 351506

Please see Performance of CLR Integration:

This topic discusses some of the design choices that enhance the performance of Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR).

Upvotes: 5

Related Questions