• Lots of NOLOCK in there. That's scary.

    One thing you can do that will immediately help performance, whether you keep the recompile in place or not. The IF statement puts the query down two different paths. When you run the query, you're going to get both paths compiled at the same time (and every time if you put RECOMPILE on it). I would suggest keeping this query as a wrapper and then put each of the individual paths within the IF statement into separate procedures that you then call from this query. That will allow each of them to either generate a specific plan when called, or, if you still want to keep the RECOMPILE in place, each will only be recompiled as called.

    Other than that, I don't see any other immediately obvious tuning opportunities. I'd need to see the execution plans, more specifically the actual plans, to make any other suggestions.

    And I would strongly recommend getting rid of the NOLOCK hints all over the place. Heck, even if you're willing to put up with the potential for extra rows or missing rows, just set the transaction isolation level to READ_UNCOMMITTED. It'll make your code cleaner and it'll make it much easier to back out if you start to see bad data due to the lack of locking.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning