Click here to monitor SSC
  • Av rating:
  • Total votes: 32
  • Total comments: 2
Greg Larsen

Choosing between CLR and T-SQL stored procedures: a simple benchmark

24 April 2007

In my previous article (Building my First SQL Server 2005 CLR) I wrote about my experiences coding and implementing my first SQL Server 2005 CLR procedure. The idea was to:

  • Provide a "learn through my pain" tutorial for people considering adoption of CLR functionality
  • Demonstrate what I considered a realistic practical usage of CLR procedures, in replacing an unsupported extended stored procedure (SP) called xp_getfiledetails. I'd used this XP in SQL 2000 to allow me to obtain various bits of operating system information for a physical disk file.

A lot of interesting points came out of the subsequent discussion on this article. At the time, I felt that a potential drawback of my new CLR-based solution was that for the first time I would have objects in my database for which the source code was stored outside of the database. This misconception was corrected by Adam Machanic. Adam pointed out a couple of ways to get your source code stored in SQL Server, one of those being to use the ALTER ASSEMBLY T-SQL command, like so:

  ALTER ASSEMBLY xp_getfiledetails
  ADD FILE FROM 'c:\temp\xp_getfiledetails.cs'

After issuing the above command the source code for my CLR can be viewed by using the sys.assemble_files catalog view. With the CLR and source code stored in SQL Server I can move my database and the CLR and source code will move along with the database.

Additionally the waters were "muddied further" by comments from Phil Factor where he offered a T-SQL solution that accomplished exactly the same thing, thus removing the need for introducing CLR functionality in the first place – although, this alternative solution was supported using OLE Automation.

So, when faced with two apparently viable solutions for a problem – one CLR-based and one T-SQL-based – how does one choose which one to use? As a starting point, I decided to perform a simple performance benchmark.

Retrieving file information using a CLR procedure

I will not present again here my original xp_getfiledetails CLR procedure solution. However, the C# code is included in the code download for this article, and is described in full in my original article.

Retrieving file information using T-SQL and OLE Automation

The basis for my T-SQL code solution was provided by Phil Factor. I modified Phil's code slightly so that the T-SQL code performs and produces exactly the same results as my CLR-based solution. Both the CLR and the T-SQL procedures produce the same results as the deprecated xp_getfiledetails extended stored procedure provided with SQL Server 2000. If you want to follow along, grab the code for the tsql_getfiledetails_OLE stored procedure, and create it in your test database.

If you plan to execute the above stored procedure you will need to enable OLE Automation. To do that you can run the following code:

sp_configure 'show advanced options', 1;
sp_configure 'Ole Automation Procedures', 1;

Benchmarking the solutions

In my benchmark testing I ran these two processes on a standalone SQL Server 2005 machine. I recorded and compared the elapsed time for each process to return file information.

Here is the simple test harness I used to drive this performance testing:

SET nocount ON

@maxsize INT
@x INT
@starttime1 datetime
DECLARE @stoptime1 datetime
DECLARE @starttime2 datetime
DECLARE @stoptime2 datetime

[Alternate Name] VARCHAR(100),
[Size] INT,
[Creation Date] CHAR(8),
[Creation Time] CHAR(6),
[Last Written Date] CHAR( 8),
[Last Written Time] CHAR(6),
[Last Accessed Date] CHAR(8),
[Last Accessed Time] CHAR(6),
[Attributes] INT

SET @x 
SET @starttime1 GETDATE()
WHILE @x 20
@x @x 1
EXEC master.dbo.tsql_getfiledetails_OLE 'C:\temp\longfilename.txt'

@stoptime1 GETDATE()
SET @x 
SET @starttime2 GETDATE()

WHILE @x 20
@x @x 1
INSERT INTO #temp EXEC master.dbo.xp_getfiledetails 'C:\temp\longfilename.txt'

@stoptime2 GETDATE()
SELECT DATEDIFF(ms,@starttime1, @stoptime1ole_duration


As you can see, this code has two WHILE loops. The first loop executes the tsql_getfiledetailes_OLE T-SQL stored procedure twenty times, and the second loop does the same for the CLR store procedure. In each case, we return the details of the same file (c:\temp\longfilename.txt).

I capture the start and end time for each WHILE loop and then execute a SELECT statement that calculates and displays the duration, in milliseconds, for each process.

When I ran this code on my standalone SQL Server, I got the following output:

ole_duration clr_duration
------------ ------------
533 16

Clearly, the CLR-based procedure is much faster at returning file information from the operating system than the T-SQL OLE automation procedure. From this test we can conclude that OLE Automation has more overhead than the different methods used in C# for returning file information.


At first glance it may seem obvious that you'd choose to implement the CLR-based procedure in your system, over the T-SQL procedure. However, is raw speed the only consideration?

On reflection, it probably isn't too surprising that the OLE T-SQL solution is slower. OLE automation is generally used for activities such as printing, sending email, saving stuff to files and so on – slow processes by definition. In our shop, the process that uses xp_getfiledetails only calls the routine once, and runs as a batch application. Therefore the extra overhead of using the T-SQL solution is really not an issue.

More important considerations in our case are ease of development and ease of maintenance and from this standpoint we'd probably end up choosing the T-SQL solution regardless of the slower performance.

On the other hand, if you where building a real-time solution that was traversing a directory tree and summarizing the size of each file so it could present a total size of the directory, then probably the CLR solution would be more appropriate.


From my benchmark testing, it is clear that the CLR method of is much more efficient at returning operating system file information. If your goal is to optimize the performance of returning file information then the CLR approach is the way to go.

However, while benchmark testing is an important element in deciding your approach, it is also clear that speed of code execution is not the only consideration when deciding whether to implement programming logic using a CLR or T-SQL. Most shops will probably find it easier to build, debug and maintain a T-SQL procedure over a CLR-based procedure. One needs to weigh all the benefits and drawbacks of any solution prior to implementation.

For additional information on how to weigh the strengths and weakness of CLR over T-SQL you might consider reading the following white paper from Microsoft:

Greg Larsen

Author profile:

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held five different DBA jobs and managed a number of different database management systems. Currently works as a DBA for Department of Health in Washington State managing SQL Server databases, and also does part-time consulting. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He also is a SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at

Search for other articles by Greg Larsen

Rate this article:   Avg rating: from a total of 32 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: CLR vs OLE
Posted by: Anonymous (not signed in)
Posted on: Friday, June 6, 2008 at 2:48 PM
Message: Did you restart the server (clear caches) and reverse the execution order?

If you're running on the same tables, naturally the 2nd set would be faster, working off of the results of the first.

But, since CLR is supposed tobe closer to metal than tsql, I'm not surprised.

Subject: After your getfilesdetails SP is run, sp_send_dbmail is failing
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 11, 2008 at 6:45 AM
Message: hiya,

dont know if you have seen this happen before but we were using your code for getfiledetails and straight after running a sp_send_dbmail for DatabaseMail.

If the sendmail has a query embedded, it fails with the below error but if we run the sendmail on its on it works, so it isnt the sendmail code. Its almost like the getfiledetails code is breaking it.

If I run the sendmail with no query it works fine.

I have tried various queries and they all fail.

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Any help with this would be much appreciated.


Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

Clone, Sweet Clone: Database Provisioning Made Easy?
 One of the difficulties of designing a completely different type of development tool such as SQL Clone... Read more...

Database Lifecycle Management: Deployment and Release
 So often, the unexpected delays in delivering database code are more likely to happen after the... Read more...

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters
 Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is... Read more...

Issue Tracking for Databases
 Any database development project will be hard to manage without a system for reporting bugs in the... Read more...

Releasing Databases in VSTS with Redgate SQL CI and Octopus Deploy
 You can still do Database Lifecycle Management (DLM) workflows in the hosted version of Team foundation... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.