Premature Optimization and LINQ to SQL

Don’t count your chickens before they hatch.  Let’s call that adage the “count-no” principle.  I promise that will be funny later.

The common interpretation of this adage is that you should not rely on future results when making decisions in the present.  It is generally an admonishment to not be too cocky about the future.  What might the corollary be?  Perhaps to concern ourselves with the details of the present moment rather than thinking about the future.  We’ll call this corollary “nocount”.  One specific example of this corollary is the programmer’s maxim about premature optimization.  In fact, it was Donald Knuth who said, “"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil,” in a response to Dijkstra’s “Go to statement considered harmful”.

So, I’m debugging a particularly pernicious problem today.  In production (and only production) we were getting a ChangeConflictException when updating a row in the database.  Unfortunately, the logs indicated that there were no MemberChangeConflict objects.  In other words we weren’t violating optimistic concurrency.  So, I profiled the SQL statements and it appeared to be just what I expected, a simple update supporting optimistic concurrency.

Did you know you can eek out an exceedingly miniscule amount of performance by using the “SET NOCOUNT ON” option with SQL Server?  Did you know that you can actually set this option at the server level? Do you know what happens when your LINQ to SQL DataContext uses the row count returned from your optimistically concurrent update statement to determine if one and only one row was updated?

Yes dear reader, premature optimization can cause the most insidious and difficult to discover problems.  Please, don’t ever violate the “nocount princple”!

HOWTO: SQL Server Return Value with Enterprise Library

    int returncode = 0;
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("<your procedure>");
    db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int32, ParameterDirection.ReturnValue, String.Empty, DataRowVersion.Default, null);
    db.ExecuteNonQuery(dbCommand);
    if (!Int32.TryParse(Convert.ToString(dbCommand.Parameters["@RETURN_VALUE"].Value), out returncode))
        returncode = -1;

The returncode local variable is useful, but generally not necessary. It's good to state your assumptions about the operation of external code explicitly.

Calling a WCF Service from a SQL Server 2005 CLR User-Defined Function

On my current project I developed a Membership Service that consolidates all user identities into a single authority, allowing for things like enterprise single sign-on, impersonation, and centralized auditing.  Now that we're ready to convert our users over from the previous system, we need to register them all through the service interface.  Since the existing user data will be staged to a SQL Server, it was appropriate to give that import access to the service.

Enter a user-defined function.  Adding a SQL Server Project to my Membership Service solution, I quickly wrapped the call to the RegisterUser web method in a SqlFunction, i.e. a method tagged with the Microsoft.SqlServer.Server.SqlFunction attribute.  David Hayden has a good post on the steps necessary to get this right.  Here's an even more detailed discussion to get you going.   In addition to these two posts, this article covers how to impersonate the principal in calling your function; it also discusses how you can modify the proxy class to not require the UNSAFE permission set.  The SQLCLR team also has a great post on doing this.

All of these authors used wsdl.exe to generate the proxy class for the web service.  This is a fine approach, but I prefer the ease of adding a Web Service to my project.

Both approaches also used the sgen utility to generate an XML Serialization Assembly for the classes in the web service proxy that was created with wsdl.exe or adding a Web Reference.  This knowledge base article explains why this is absolutely necessary when hosting your proxy in SQLCLR.

There is an addition consideration with using wsdl.exe and/or Web Reference generated proxies against a WCF Service that uses the wsHttpBinding, highlighted here.  These proxies only support the basicHttpBinding, that is they don't support WS-Addressing as required by the wsHttpBinding.  So, if you got your sql clr function or stored procedure calling your WCF web service, but you are getting a web service timeout, check to make sure it is using a compatible binding.  You may also notice the System.ServiceModel.ActionMismatchAddressingException in the service log.

Using SqlCacheDependency with SQL Server 2005 in ASP.NET 2.0

Some time ago I spent an afternoon hunting around the web for the details on how the SqlCacheDependency in ASP.NET 2.0 works with SQL Server 2005. It turns out that the class performs an automatic configuration of a SQL Server 2005 query notification. Not sure what that is?Using and Monitoring Sql Query Notifications This is a truly excellent article to get started.

I've included the relevant links to SQL Books Online and quoted particularly relevant passages in italics. I certainly don't intend this post to be a "how-to", but I hope it helps the reader to get her arms around how to use the SqlCacheDependency and query notifications (by extension).

First, we only need to enable the service broker. Notification services is not utilized, surprisingly.

Using Query Notifications

The Database Engine uses Service Broker to deliver notification messages. Therefore, Service Broker must be active in the database where the application requests the subscription. The query notification functionality does not require or use Notification Services. Query notifications are independent of event notifications.

Secondly, we cannot use query notifications for just any type of query. There are a lot of rules on how the query is constructed that my restrict what data you can cache with the SqlCacheDependency.

Creating a Query for Notification

The query notifications functionality builds on the change detection mechanisms that the Database Engine uses to maintain indexed views. The requirements and restrictions for statements in a query for notification are similar to the requirements and restrictions for an indexed view.

It seems the overhead associated with these query notifications is mainly on updates. I'm guessing that SQL Server uses a mechanism similar to its query plan caching/reuse mechanism to identify similar queries in subscriptions.

Planning for Notifications

When query notifications are active on a table, updates to the table are more expensive. The Database Engine performs extra work to check subscriptions and, if necessary, generate notifications. Reusing internal templates helps to minimize the overhead per subscription. Therefore, you should use query notifications only for applications that submit queries with a similar structure. An application that submits queries with different structures should not use query notifications.

In addition to having normal execute and select permissions, the application user needs to have a couple of other permissions. Although granting DDL rights to the application seems deeply wrong to me, there is a wonderful article on granting minimum rights to the application.You can read more about them here.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <database_principal>

GRANT RECEIVE ON QueryNotificationErrorsQueue TO <database_principal>

Because the SqlDependency object in the ASP.NET runtime auto-magically creates the Service Broker service, queue, and notification procedure, the application user also needs the following rights.

GRANT CREATE PROCEDURE TO <database_principal>

GRANT CREATE QUEUE TO <database_principal>

GRANT CREATE SERVICE TO <database_principal>

Actually, the class in the ASP.NET runtime that actually sets all this up is called the SqlConnectionContainer. It's quite the sophisticated class, doing all of the heavy-lifting. From what I gather, it creates procedures (named SqlQueryNotificationStoredProcedure-* where * is a GUID) that drop the service and queue when the conversation timer expires. I'm a little fuzzy on the low-level implementation, but I wanted to know why it needed these rights. So, I used Lutz Roeder's .NET Reflector to take a peek at what is going on inside the class.

The SQL Books online mentions that the user that attaches a query notification request to a command must have the following:

GRANT SEND ON SERVICE:://theservice to <database_principal>

However, the service doesn't exist until the SqlConnectionContainer creates it, so the SqlConnectionContainer grants the SEND ON SERVICE to a specific role: sql_dependency_subscriber. This role does not exist and must be created if you need to lock-down permissions as in the article above.