2

I am trying to optimize the inserting of some data into our database, but I'm not too sure if this stored procedure would be more efficient if it was split out into multiple stored procedures.

I have a stored procedure that inserts data into multiple different tables. The stored procedure will check a key in the provided data, and then go over the if statements to perform inserts into one table. It will never insert into more than one table each call.

Would it be quicker to split this logic, and have a stored procedure for each different if statement?

For scale, there are 11 if statements, and each one writes to a different table.

An example would be like so:

IF (@CakeKey = 'Chocolate')
BEGIN
INSERT INTO [CholocateCakeTable]
...
END
ELSE IF (@CakeKey = 'Vanilla')
BEGIN
INSERT INTO [VanillaCakeTable]
...
END
ELSE IF (@CakeKey = 'Strawberry')
BEGIN
INSERT INTO [StrawberryCakeTable]
...
END
ELSE IF (@CakeKey = 'Caramel')
BEGIN
INSERT INTO [CaramelCakeTable]
...
END

The change would mean each if is in a different stored procedure with the if clause no longer being needed.

(The stored procedure is called from C#, so the if checks would be done there instead.

3
  • 1
    If you mean that muiltiple SPs is "the application code checks for the value and executes according SP" then, of course, multiple-SP variant will be more fast. If you mean that SQL code in upper SP will check and call according sub-SP then single SP will be more fast. PS. Use not a chain of IFs but CASE statement: CASE @CakeKey WHEN 'A' THEN CALL a; WHEN 'B' THEN CALL b; ... END;
    – Akina
    Sep 9, 2022 at 4:28
  • With what your procedure is actually doing, there's probably going to be no measurable difference either way. There are some specific cases where the use of an IF statement can be used to optimize parameter sniffing issues in stored procedures, but that's a completely different use case then what you're asking about. But it'll probably be cleaner / better to maintain to have a dedicated procedure for each different INSERT use case, and let the app decide which one to use as it needs.
    – J.D.
    Sep 9, 2022 at 12:39
  • The performance issue is probably that you have similar things going into separate tables.
    – Rick James
    Sep 10, 2022 at 0:51

1 Answer 1

1

The IF has to happen somewhere, either in C# or T-SQL. Neither is going to be measurably faster than the other. So the total latency the end user observes will be the same.

Now, if you were to instrument your environment and measure to the CPU clock tick then you may see a difference. Likely C# has a better compiler than T-SQL so may run IFs faster. The network packets will be shorter by not having @CakeId so may have better throughput and latency. Each new SP will be smaller so may be subject to fewer recompilations. But, frankly, I doubt it.

You'd be better off tuning individual queries or removing redundant indexes or optimising the number of calls the app makes.

Or, you know, team bonding over some of those cakes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.