\nTo 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.\n<\/p>\n
\nAnother 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:\n<\/p>\n
\nCREATE PROCEDURE getauthors
\nAS
\nSELECT * FROM authors\n<\/p>\n
\n
\nthis .NET code looked pretty straightforward to me:\n<\/p>\n
\npublic static void GetAuthors1()
\n{
\n \/\/ create and initialize command
\n SqlCommand cmd = SqlContext.GetCommand();
\n cmd.CommandText = "select * from authors";\n<\/p>\n
\n \/\/ get data reader
\n SqlDataReader rdr = cmd.ExecuteReader();\n<\/p>\n
\n \/\/ pass results to client
\n SqlContext.GetPipe().Send(rdr);
\n}\n<\/p>\n
\nThis 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.\n<\/p>\n
\nThe 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.\n<\/p>\n
\n\/\/ initialized once during class construction
\nreadonly static SqlDefinition def =
\n new SqlDefinition("select * from authors", null, null);\n<\/p>\n
\npublic static void GetAuthors2()
\n{
\n \/\/ lookup static part of command
\n \/\/ in this example, the command is entirely static
\n SqlExecutionContext ec =
\n SqlContext.GetConnection().CreateExecutionContext(def);\n<\/p>\n
\n \/\/ execute it. no SqlDataReader needed, results to client
\n SqlContext.GetPipe().Execute(ec);
\n}\n<\/p>\n
\nAt 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.\n<\/p>\n
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,38],"tags":[],"class_list":["post-1038","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sqlclr"],"yoast_head":"\n