Currently I have a procedure that performs 4 operations in the Transaction:

  1. Update record in table A
  2. Update record in table B
  3. Insert record into table C
  4. Delete record from table D

The first operation is performed only when one of the parameters of procedure (@IsUpdateA) is equal to 1.

So from business perspective it looks If @IsUpdateA = 1, the update of table A has to be performed, while step 2, 3 and 4 are always performed.

The question is whether it is better (in terms of efficiency/execution plan) to split such procedure into two, i.e.

  1. First procedure will always perform steps 1,2,3,4
  2. Second procedure will always perform steps 2,3,4

And @IsUpdateA will be checked in application. I can not use procedure in procedure (internal requirement).

  • you can have it done at the SQL level... you can have your proc to check if @IsUpdateA =1 and them launch a store proc that would only do the step 1 and finally, call another proc that will run step 2-4. So you will end up with 3 proc. The "launcher" which will launch one or 2 other proc (one being step 1 and the other being step 2-3-4) – Dominique Boucher Sep 8 '20 at 13:48
  • Yep, that is true. I did not mention that one of our agreement is that we are not using procedure in procedure (internal requirement). That is why I have only two options described above. – axdna Sep 8 '20 at 13:53
  • I see. If you could share the execution plan so that we can have an idea of the work that this represent, that will help us to give you a better recommandation . – Dominique Boucher Sep 8 '20 at 13:56

The optimizer uses the sniffed parameter values regardless of your procedural logic. Imagine your proc code with only the DML statements and the parameters in it.

So there is a risk that when the plan is generated, the values to be used for the UPDATE are nonsense values when @IsUpdateA = 0. From a performance perspective, that is. Think of the WHERE clause and selectivity. I.e., the plan is created for that UPDATE (which isn't executed) is based on values that give you incorrect selectivity calculation and as a result a bad plan.

Or the other way around, of course.

I.e., having two procs can give you better estimates and in the end better plans.

  • Thank you for answer! So from my understanding If we have one procedure for operations 1,2,3 and 4 and parameter isUpdateA is equal to 0 than it is possibilty than execution plan will not be efficient? – axdna Sep 8 '20 at 21:39
  • Yes, that is correct. – Tibor Karaszi Sep 9 '20 at 7:32
  • Would like also to ask. The same situation will be when we have procedure InsertInfo with parameters @InsertAlsoToTableC. So we have table A,B and C. Procedure has several parameters which are values for columns in table A,B and C. Procedure is responsible for insert for table A,B and C. But insert do table C is optional (70% will have this insert). Better approach is to divide it for 2 procedures ? – axdna Sep 21 '20 at 12:07
  • If there are simple inserts, then it probably won't matter, since the execution plans won't change based on the values you pass. But if there are inserts with subqueries, then my points above applies. – Tibor Karaszi Sep 21 '20 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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