SQLCLR Optimizations – 2

To squeeze every ounce of performance out of the SqlServer data provider, the rule is to allocate the least number of ancillary object instances possible. In SQLCLR Optimizations 1, I mentioned a mechanism to keep from creating a SqlCommand and associated objects, just to initialize a SqlDefinition.

Another performance increase comes by not allocating/using extra buffers. When you execute a statement that produces a rowset, this means using SqlDataReader only when you need it. You need it only when you are  going to consume the results IN the .NET procedure and do some processing with them. You don't need a SqlDataReader just to pass a rowset back to the client. As an example, if I want to emulate this trivial T-SQL proc:

SELECT * FROM authors

this .NET code looked pretty straightforward to me:

public static void GetAuthors1()
  // create and initialize command
  SqlCommand cmd = SqlContext.GetCommand();
  cmd.CommandText = "select * from authors";

  // get data reader
  SqlDataReader rdr = cmd.ExecuteReader();

  // pass results to client

This actually allocates enough of a buffer in your procedure to hold a row and reads into the buffer, just to turn around and hand it to the SqlPipe. That buffer is pure overhead. A more performant way, that basically points the rowset at the SqlPipe, is to use SqlPipe.Execute. You can Execute a SqlCommand or use a SqlExecutionContext.

The SqlExecutionContext is the class that represents the volitile parts of SqlCommand, the parameters, and contains methods to execute SQL and return results. Here's the same procedure, using both SqlDefinition/SqlExecutionContext and SqlPipe.Execute.

// initialized once during class construction
readonly static SqlDefinition def =
  new SqlDefinition("select * from authors", null, null);

public static void GetAuthors2()
  // lookup static part of command
  // in this example, the command is entirely static
  SqlExecutionContext ec =

  // execute it. no SqlDataReader needed, results to client

At first glance, you look at System.Data.SqlServer and see a few classes that look strange, but most everything looks the same as with the SqlClient provider. The big perf improvements, however, come from using the classes that look strange and are different. That's why they are there, to allow efficiencies that you can't get (like not allocating a buffer to receive a rowset) on the client. But you wouldn't send a rowset back to the "client" from the client, this only happens in a procedure inside SQL Server. On the server it's a whole different ballgame.


One thought on “SQLCLR Optimizations – 2

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.