\nGood T-SQL programmers are always looking to optimize their procedural code. Little optimizations can end up as big savings if the code will be executed many times or if it's used in many places in the application. Good SQLCLR programmers will be doing this too. Recently I ran into a micro-optimization that has promise.\n<\/p>\n
\nEveryone who's used SQLCLR in earnest knows by now that it's useful to separate SqlCommand into two pieces: SqlDefinition and SqlExecutionContext. SqlDefinition is the static portion of the command, SqlExecutionContext contains the execution methods like ExecuteNonQuery and friends. Last time I'd heard rumors, about a 20% performance improvement, although it's not usually useful (or not allowed in betas) to report performance numbers. It's beta software after all, possibly with extra unoptimized code that will be removed\/optimized at release. Especially SQLCLR, a brand new feature. So I didn't confirm the rumored numbers; sounded like a reasonable assumption.\n<\/p>\n
\nSqlDefinitions are best initialized in the class constructor and stashed in readonly static (Shared Readonly in VB.NET) variables. I've done it like this:\n<\/p>\n
\nstatic readonly SqlDefinition def = null;\n<\/p>\n
\nstatic MyClass() {
\n SqlCommand cmd = SqlContext.GetCommand();
\n cmd.CommandText =
\n "select * from authors where au_id like @au_id";
\n cmd.Parameters.Add("@au_id", SqlDbType.Varchar);
\n cmd.Parameters[0].Size = 50;
\n def = new SqlDefinition(cmd);
\n}\n<\/p>\n
\nLately, I've noticed there's an even better way. There's a constructor for SqlDefinition that takes a CommandText, CommandType, array of SqlMetaData, and array of ParameterDirection. I can even put the initialization of these in the variable declarations, like this:\n<\/p>\n
\nreadonly static SqlMetaData[] md =
\n new SqlMetaData[1] { new SqlMetaData("@au_id", SqlDbType.VarChar, 50) };
\nreadonly static ParameterDirection[] pd =
\n new ParameterDirection[1] { ParameterDirection.Input };
\nreadonly static SqlDefinition def = new SqlDefinition(
\n "select * from authors where au_id like @au_id",
\n CommandType.Text, md, pd);\n<\/p>\n
\nThe variables must be defined in this order, as the SqlDefinition depends on the other variables being initialized first. Or I could initialize them explicitly in the class constructor.\n<\/p>\n
\nDoing it this way saves me from creating object instances, then throwing them away to the garbage collector. Namely:
\n1. 1 SqlCommand
\n2. 1 SqlParameterCollection
\n3. N SqlParameters where N is the number of parms in the query. SqlParameter actually contains SqlMetaData as a member, plus "other stuff"\n<\/p>\n
\nBecause I'm only executing this ONCE (the first time the class is instantiated) in an entire SQL Server run, is this a micro-optimization? Probably. But what if I have 100 or 1000 such SqlDefinitions? Maybe not as "micro" then… what do you think?<\/p>\n","protected":false},"excerpt":{"rendered":"
Good T-SQL programmers are always looking to optimize their procedural code. Little optimizations can end up as big savings if the code will be executed many times or if it's used in many places in the application. Good SQLCLR programmers will be doing this too. Recently I ran into a micro-optimization that has promise. Everyone […]<\/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-1040","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sqlclr"],"yoast_head":"\n