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:
CREATE PROCEDURE getauthors
AS
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
SqlContext.GetPipe().Send(rdr);
}
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 =
SqlContext.GetConnection().CreateExecutionContext(def);
// execute it. no SqlDataReader needed, results to client
SqlContext.GetPipe().Execute(ec);
}
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.