4

We have a core stored procedure that's a critical part of our system and gets called thousands of times every hour to handle important operations. Now, there's a new application being introduced that needs to use this procedure but requires some extra logic that the current apps don't need.

A colleague suggested that we just modify the existing procedure by adding a condition like:

IF @Source = 'new_app' THEN  
   -- run additional logic

Their idea is to reuse the same procedure for consistency. But I’m concerned that even a small conditional like this, when executed thousands of times per hour, could add unnecessary overhead, especially when the majority of the calls don’t need this logic.

I suggested we create a separate stored procedure for the new application’s use case to keep things clean, focused, and possibly better for performance.

Is separating the logic into its own procedure a good choice or are there any better alternatives? Would appreciate any thoughts or best practices you've come across. Thanks!

Improve this question
0

3 Answers 3

Reset to default
20

dependent, see

It really depends on what would take place inside the IF branch, which you've conveniently omitted any details about. There are of course potential concerns about compilation time, plan complexity, and other things. The main problem you may find yourself dealing with is plan quality because of parameter usage and cardinality estimation.

when it wouldn't matter

Let's say you have a stored procedure with one parameter used as a search predicate, and two IF branches that both use that parameter.

CREATE OR ALTER PROCEDURE
    dbo.TwoBranches
(
    @DisplayName nvarchar(40),
    @WhichWay bit = 0
)
AS
BEGIN
    IF @WhichWay = 0
    BEGIN
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Users AS u
        WHERE u.DisplayName = @DisplayName;
    END;

    IF @WhichWay = 1
    BEGIN
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Users AS u
        JOIN dbo.Posts AS p
          ON p.OwnerUserId = u.Id
        WHERE u.DisplayName = @DisplayName;    
    END;
END;

When SQL Server compiles this stored procedure, both queries will have an execution plan generated, even though only one IF branch will be executed at runtime. This includes cardinality estimation for both queries, based on the compile-time value for the @DisplayName parameter.

The reason why this is okay here is that both branches make use of it. Whether parameter sensitivity is an issue or not is somewhat irrelevant, though always worth keeping in mind.

Let's look at a different situation!

when it would matter

For this stored procedure, we have two branches, and each branch makes use of a different search parameter.

CREATE OR ALTER PROCEDURE
    dbo.TwoBranches
(
    @Reputation integer = NULL,
    @Score integer = NULL,
    @WhichWay bit = 0
)
AS
BEGIN
    IF @WhichWay = 0
    BEGIN
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Users AS u
        JOIN dbo.Posts AS p
          ON p.OwnerUserId = u.Id
        WHERE u.Reputation = @Reputation
    END;

    IF @WhichWay = 1
    BEGIN
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Users AS u
        JOIN dbo.Posts AS p
          ON p.OwnerUserId = u.Id
        WHERE p.Score = @Score
    END;
END;

Now when the stored procedure is compiled, one search parameter will likely be NULL, or a canary value like 0. One plan will get cardinality estimation based on the "real" search parameter value, let's say @Reputation = 1000, and @Score will get cardinality estimation based on NULL or the canary value. This could very well be a disaster.

you're probably right

Putting the branched code for the new application into either a new stored procedure that gets called in the IF branch for the new application, or using parameterized dynamic SQL, will hide it away enough during compile-time, but do just fine at execution time.

further reading

Improve this answer
0
7

The overhead of a single IF check inside a stored procedure is almost negligible; it won’t meaningfully affect CPU usage, even when the procedure is called thousands of times per hour. The real concern is not the cost of the conditional itself but the impact on query plan generation. When a procedure contains conditional branches with significantly different logic, SQL Server may try to create one plan that serves both paths, which can lead to parameter sniffing issues or suboptimal execution plans being reused.

From a maintainability standpoint, adding special-case logic into a critical core procedure can quickly bloat the code and mix unrelated concerns. This makes it harder to read, test, and evolve, while also introducing the risk that future changes for one use case might unintentionally affect the other. Separating the logic into its own procedure avoids these pitfalls and allows SQL Server to cache and optimize execution plans independently for each workload.

A common best practice in this situation is to leave the current procedure untouched for existing applications and introduce a new one for the new application. If both need to share some common operations, you can refactor those into a smaller “core” procedure and have each application-specific procedure call it, adding their own extra logic around it. This keeps the code base cleaner, reduces the risk of plan instability, and provides flexibility to extend the new application’s behavior without interfering with the existing workload.

Improve this answer
0
2

But I’m concerned that even a small conditional like this, when executed thousands of times per hour, could add unnecessary overhead, especially when the majority of the calls don’t need this logic.

No I don't think that's a plausible concern at all. Testing a single parameter once a second is not something that would be considered an overhead under normal circumstances.

Is separating the logic into its own procedure a good choice or are there any better alternatives?

What your colleague has proposed - parameterising an existing procedure - is quite normal.

Separating the logic is also quite plausible if there are such significant differences in logic that it no longer makes sense to share it, or if it needs to be possible to vary the logic independently according to the caller (for example, if client-side applications have to marry with the logic inside the procedure, then you might want the possibility of updating different applications at different times).

You'd have to decide what suits your exact circumstances, but both approaches are regularly used as found appropriate. Many intelligent judgements have to be made by developers about organising code, not just accounting for their own cognitive capabilities or professional preferences, but also perceiving and considering a large number of subtleties about their particular situation.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.