Four years later, a retrospective

Welcome back!  I’ve joined a few of my coworkers in a weekly blog challenge, to write a new post each week.  To start, I figured I should finally write the post I meant to write four years ago, about a small piece of code that saved our rear ends from a huge issue plaguing our production environment. 

Right about this time four years ago, our busy season was just beginning, and we had recently deployed our custom built phone call distributer, Complemax.  We started seeing an issue crop up that was so severe, Complemax effectively stopped routing calls in our call center.  Complemax works by building a grid of phone calls and agents, and scoring each match.  Then the calls are routed to the best matching agents.  After digging through logs, we found that Complemax was processing messages fine, calls were being added and removed as they went on hold or hung up, and employees were updating as they came online or changed their status, but the grid wasn’t being scored! 

So now we had found the root of the problem, but what was the cause?  After analyzing memory dumps, digging through the code, and learning more about low level debugging than I ever had, we finally found the issue was caused by our threading.  The grid makes use of a ReaderWriterLockSlim as its state is updated.  Every five seconds, a read lock would be taken so the current matches could be scored, during which pending updates would wait a short time.  Unfortunately, that lock prioritizes writes over reads, causing our all-important grid calculation thread to be starved by the large amount of updates that were coming in as we received more and more phone calls.

How would we prioritize the read lock, to freeze the grid while we looped through it?  The solution needed to be simple, so we could fix the issue in production as fast as possible.  That’s when I came up with the idea of a “dual” reader-writer lock.  Use two locks on top of each other to give the less common read operation higher priority.  When an update is needed, take a read lock on the first lock and then a write lock on the second lock.  When the grid needs to be frozen, take a write lock on the first lock and pending “reads” on that lock will wait, preventing more updates to the grid.

I’m sure if we had caught the issue before it reached production, we would’ve ended up using something other than a ReaderWriterLockSlim in the first place, but surprisingly in the four years since we added the “dual” lock, we haven’t changed it.  It’s simple, it works, and it handles the load thrown at it.  It was certainly a good learning experience, one I’m glad I had once, but not one I’d ask for.

PostSharp RC3 MSBuild/Code Analysis error

I’ve been meaning to write this post ever since David and I came across this error after we installed the new PostSharp RC3 released a few weeks ago.  After building a project and running code analysis on it, we received the following error: “CA0055 : * Could not load file: ‘@(IntermediateAssembly->’obj\Debug\Before-PostSharp\ExtendHealth.Utilities.dll’.”  After a little digging, we found a small typo in the latest .targets file included in the PostSharp install.  A couple characters are missing from line 186 of PostSharp-1.0.targets:

<CreateProperty Value=”@(IntermediateAssembly->’$(_PostSharpInputBin)‘)“>

The ‘ and ) in red above were missing.  After we fixed the typo, code analysis worked perfectly.  This typo has been fixed in the latest daily build (1.0.9.377), but that build was emitting invalid IL and causing InvalidProgramExceptions to be thrown.  It’s simple to fix, so we’ve just remembered to do it on each machine using the latest release candidate.  The default location of the targets file is “%ProgramFiles%\PostSharp 1.0”.

NOTE: If running Vista, be sure to run the text editor with administrative privileges to update the file.

Function Mapping with Inherited Types

This post was inspired by a post on the Entity Framework forums asking for an example of mapping functions to a base type and its inherited types.  I’m going to show two ways to go about it with a simple model.  Note that this isn’t necessarily the best data model, it’s just a Table-Per-Hierarchy (TPH) example.  We have a top-level Person type with an Employee subtype, which subsequently has a Manager subtype, defined in one table:

CREATE TABLE [dbo].[Person](
    [PersonId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [PersonTypeId] [int] NOT NULL,
    [EmployeeTypeId] [int] NULL,
    [HireDate] [datetime] NULL,
    [TeamName] [varchar](50) NULL,
    [OfficeLocation] [varchar](50) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

The Entity Model is defined below:

FunctionMappingModel

There are at least two ways to map the three entities to CUD stored procedures.  We could create one procedure per table, or we could create one procedure per type.  In this post I’ll show how to map the entities in the hierarchy to the same three stored procedures, which I generated using the T4 template David DeWinter and I developed.  The Insert, Update, and Delete stored procedures are defined as follows:

CREATE PROCEDURE “dbo”.”zsp_Person_Insert”
(
    @FirstName varchar(50),
    @PersonTypeId int,
    @EmployeeTypeId int,
    @HireDate datetime,
    @TeamName varchar(50),
    @OfficeLocation varchar(50)
)
AS
BEGIN
        INSERT INTO “dbo”.”Person”
         (
            “FirstName”,
            “PersonTypeId”,
            “EmployeeTypeId”,
            “HireDate”,
            “TeamName”,
            “OfficeLocation”
        )
        VALUES
        (
            @FirstName,
            @PersonTypeId,
            @EmployeeTypeId,
            @HireDate,
            @TeamName,
            @OfficeLocation
        )
        SELECT SCOPE_IDENTITY() AS PersonId
END
GO

CREATE PROCEDURE “dbo”.”zsp_Person_Update”
(
    @PersonId int,
    @FirstName varchar(50),
    @PersonTypeId int,
    @EmployeeTypeId int,
    @HireDate datetime,
    @TeamName varchar(50),
    @OfficeLocation varchar(50)
)
AS
BEGIN
    UPDATE “dbo”.”Person”
    SET
            “FirstName” = @FirstName,
            “PersonTypeId” = @PersonTypeId,
            “EmployeeTypeId” = @EmployeeTypeId,
            “HireDate” = @HireDate,
            “TeamName” = @TeamName,
            “OfficeLocation” = @OfficeLocation
    WHERE
            “PersonId” = @PersonId
END
GO

CREATE PROCEDURE “dbo”.”zsp_Person_Delete”
(
    @PersonId int
)
AS
BEGIN
    DELETE FROM “dbo”.”Person”
    WHERE
            “PersonId” = @PersonId
END
GO

The trick in this case is to create some “wrapper” functions in the SSDL for the subtypes as Colin Meek suggested in this forum post.   We don’t need to create functions for deleting, because the original delete stored procedure works fine.  Here is the SSDL I ended up with:

        <Function Name=zsp_Person_Delete >

          <Parameter Name=PersonId Type=int Mode=In />

        </Function>

        <Function Name=zsp_Person_Insert >

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=PersonTypeId Type=int Mode=In />

          <Parameter Name=EmployeeTypeId Type=int Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

          <Parameter Name=OfficeLocation Type=varchar Mode=In />

        </Function>

        <Function Name=zsp_Person_Update >

          <Parameter Name=PersonId Type=int Mode=In />

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=PersonTypeId Type=int Mode=In />

          <Parameter Name=EmployeeTypeId Type=int Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

          <Parameter Name=OfficeLocation Type=varchar Mode=In />

        </Function>

        <Function Name=Employee_Insert >

          <CommandText>

            <![CDATA[exec dbo.zsp_Person_Insert @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=1, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=null]]>

          </CommandText>

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

        </Function>

        <Function Name=Employee_Update >

          <CommandText>

            <![CDATA[exec dbo.zsp_Person_Update @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=1, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=null]]>

          </CommandText>

          <Parameter Name=PersonId Type=int Mode=In />

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

        </Function>

        <Function Name=Manager_Insert >

          <CommandText>

            <![CDATA[exec dbo.zsp_Person_Insert @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=2, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=@OfficeLocation]]>

          </CommandText>

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

          <Parameter Name=OfficeLocation Type=varchar Mode=In />

        </Function>

        <Function Name=Manager_Update >

          <CommandText>

            <![CDATA[exec dbo.zsp_Person_Update @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=2, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=@OfficeLocation]]>

          </CommandText>

          <Parameter Name=PersonId Type=int Mode=In />

          <Parameter Name=FirstName Type=varchar Mode=In />

          <Parameter Name=HireDate Type=datetime Mode=In />

          <Parameter Name=TeamName Type=varchar Mode=In />

          <Parameter Name=OfficeLocation Type=varchar Mode=In />

        </Function>

These “fake” stored procedures call the real stored procedure and pass in constant values where needed, such as the columns used in the conditions of the subtypes.  After creating the functions in the SSDL, we just need to map them to the entities using the designer.  They now show up in the drop down menu in the Mapping Details window:

WrapperFunctions

We map the real stored procedures to the base Person type and ignore the extra parameters, or we could have created “wrappers” for the Person entity as well.

PersonMapping

The Employee type is mapped to the wrapper functions.  The properties inherited from Person aren’t automagically mapped, but it’s easy enough to select them from the drop down.

EmployeeMapping

And, finally, the Manager entity:

ManagerMapping

You might have noticed that the Employee and Manager insert function mappings don’t show any Result Column Bindings.  I did add them, but the designer doesn’t show them.  It’s a bug I described in my last post.  The last quirk to remember when mapping these “wrapper” functions is that they will be deleted from the SSDL after running the Update Model from Database wizard.  If this solution doesn’t seem attractive, there is another option.  You can create stored procedures for each type and map those procedures to their respective entity.  I won’t show how to do that here, but it should be fairly straightforward.

Special thanks to Dave for his original work setting up the function mappings for our large model.

New Entity Framework designer bugs in SP1 beta

David DeWinter and I have come across a few new and annoying bugs since we installed the Visual Studio Service Pack 1 beta yesterday.  The first and most annoying is detailed in a forum post in the MSDN Entity Framework forum.  The Error List window now lists fake errors for all of our subtypes that are children of abstract entities with a message similar to “Entity types B, C, D are mapped to table A without discriminating conditions on all mappings to the table.”  The project still builds and the errors can be ignored.

Another bug we found today relates to result column bindings for mapped sprocs on subtypes.  We have the results from Insert sprocs mapped to properties on the entity, such the as the Id.  These bindings don’t show up at all in the Mapping Details window, even though they exist in the MSL in the .edmx file.  At first I thought the mapping wasn’t there, and tried to add one.  After deselecting that entity and reselecting it the Mapping Details window showed no result column bindings.  I opened the file in the XML editor and looked at the mapping for that insert sproc.  There were several duplicate result column bindings there from my attempts to add it with the designer.  We’ve reported this bug at Microsoft Connect.

The last problem we ran into is not actually a bug, but a change in behavior.  Dave created a thread about it at the Entity Framework forum.  We had created the following extension method relying on the EntityKey of an EntityReference:

        /// <summary>

        /// Loads the specified <see cref=”EntityReference”/> if it is not loaded

        /// and only if there is an actual reference to load.

        /// </summary>

        /// <param name=”entityReference”>The entity reference </param>

        public static void LoadIfNeeded(this EntityReference entityReference)

        {

            // The EntityKey will be null if there is no reference.

            if (!entityReference.IsLoaded && entityReference.EntityKey != null)

            {

                entityReference.Load();

            }

        }

We had a situation where an entity shares a 0..1 relationship with another entity.  Calling Load() when that relationship was null caused an unnecessary database hit, which was exaggerated when called in a loop on several entities.  We’ve since been able to remove the need for a LoadIfNeeded() method, but it is still nice to have an EntityKey on references for other uses.

We’ll likely run into a few more caveats with the new beta, but the fixes to previous bugs are always welcome.  Hopefully it won’t be too long of a wait before another release.  One thing that surprised me was how long it took to install the service pack, much longer than the install of the .NET Framework 3.5 SP1 beta.

Partial sprocs?

Dave and I have written a T4 template to generate Entity Framework-friendly INSERT, UPDATE, and DELETE stored procedures.  I’ll detail the template in a future post.  I had the crazy idea of implementing something similar to C#’s partial methods in SQL, which I named “partial stored procedures.”  The reason behind the idea is the same reason partial methods exist: a code generator (a T4 template in our case) generates the same code every time, overwriting any changes someone might have made to the generated code.  The Entity Framework and LINQ to SQL designers create partial methods in the generated code to provide points where a developer can execute code, such as before and after a property changes.  It’s possible to do something similar when generating stored procedures, but the performance hit might be discouraging.  Here’s an example of what the code generator might create:

CREATE PROCEDURE [dbo].[Person_Insert]
    @name varchar(50)
AS
BEGIN
    IF OBJECT_ID(‘[dbo].[usp_BeforePerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_BeforePerson_Insert] @name

    INSERT INTO [Test] VALUES (@name)
    SELECT SCOPE_IDENTITY()

    IF OBJECT_ID(‘[dbo].[usp_AfterPerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_AfterPerson_Insert] @name
END

I did a small amount of testing while I played around with the idea.  Inserting 100,000 rows into the two-column table using a regular stored procedure took 31 seconds.  After adding the additional SQL, but without creating the “partial” stored procedures, 100,000 executions took 35 seconds to complete.  When I created the “before” stored procedure with a simple SELECT in it, the time rose to 54 seconds.  Although it’s an interesting idea, there isn’t really any need for something like partial methods in SQL.  The same thing can be accomplished by adding INSTEAD OF and AFTER triggers to the table.

Strange Behavior with InternalsVisibleTo

I ran into an interesting situation the other day while using the InternalsVisibleToAttribute.  Our team is developing three libraries as part of our current project, and all three of them are signed with the same key.  Assembly A makes its internals visible to Assembly B and C.  Assembly A and B build successfully, but Assembly C fails with the error:

Friend access was granted to ‘AssemblyC, PublicKey=0024000004800000…’, but the output assembly is named ‘AssemblyC, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null’. Try adding a reference to ‘AssemblyC, PublicKey=0024000004800000…’ or changing the output assembly name to match.

We obviously can’t add a reference to Assembly C in Assembly A, as that would cause a circular dependency.  The only clue we could get out of that error message is the PublicKeyToken=null bit.  Why would the PublicKeyToken be null if the assembly is signed?  I wanted to confirm that the assembly was signed with Reflector or sn.exe, but there was no dll to check because the build failed.  After commenting out all of the code that accessed the internal parts of Assembly A, we got Assembly C to build and confirmed that it was indeed signed.  From the output window, we saw that the “WorkflowCompilation” task was executing and failing, and that the “CoreCompile” wasn’t even running.  It seems that the compilation fails because Assembly C is not yet signed when the compiler attempts to verify the InternalsVisibleTo attribute on Assembly A.  We managed to get the project to build by adding the AssemblyKeyFile attribute to it:

[assembly: AssemblyKeyFile(“MyKey.snk”)]

This produces the warnings “Use command line option ‘/keyfile’ or appropriate project settings instead of ‘AssemblyKeyFile'” and “Option ‘keyfile’ overrides attribute ‘System.Reflection.AssemblyKeyFileAttribute’ given in a source file or added module” that we have to ignore.  I’ve found this problem to happen to any workflow assemblies that attempt to access the internals of another assembly.  I’ve submitted a bug report about it here.

Return Statements inside try Blocks

I recently wrote an extension method named IsNullOrEmpty() for IEnumerable<> types.  Dave had already written one all IEnumerable types:

        public static bool IsNullOrEmpty(this IEnumerable source)

        {

            if (source != null)

            {

                IEnumerator enumerator = source.GetEnumerator();

                return !enumerator.MoveNext();

            }

            return true;

        }

This method has been useful countless times, but Bek realized this could leave open database connections, or cause other problems related to not calling Dispose.  I added the overload:

        public static bool IsNullOrEmpty<T>(this IEnumerable<T> source)

        {

            if (source != null)

            {

                using (IEnumerator<T> enumerator = source.GetEnumerator())

                {

                    return !enumerator.MoveNext();

                }

            }

 

            return true;

        }

A question was raised about whether the generated finally block would be executed if the method returned inside the try block.  I couldn’t remember the answer, but a quick test with LINQPad gave us our answer:

        try

        {

            return;

        }

        finally

        {

            “Hello world”.Dump();

        }

“Hello world” was displayed in the output box.  If the finally block weren’t executed before the method returned, we’d have to store the return value in a temporary variable and place the return statement after the end of the using/finally block.  It’s one of the small tidbits that are nice to know.