Performance Issue due to GOTO statement

Question

I'm using SQL Server 2016 database project and my script is like below

DECLARE Marker NVARCHAR(50) = (SELECT Value FROM Table1 WHERE name = 'Marker') 
IF( IS NOT NULL)
BEGIN
    IF @Marker = 'Marker1' GOTO Marker2;
    IF @Marker = 'Marker2' GOTO Marker3;
    IF @Marker = 'Marker3' GOTO Marker4;
    IF @Marker = 'Marker4' GOTO Marker5;
    IF @Marker = 'Marker5' GOTO Marker6;
    IF @Marker = 'Marker6' GOTO Marker7;        
    ELSE GOTO EmptyBlock;    
END 

MARKER1:
Code for marker 1

MARKER2:
Code for marker 2    

MARKER3:
Code for marker 3
.
.
.
EmptyBlock:
PRINT 'No changes'

This script file will be executed after every deployment and based on deployment it will be skipping the previous lines and now it will be reached to marker 15.

It's taking too much time to execute even though there are few lines of code, I've finally found the issue due to GOTO statements. I don't know if using GOTO is best practice or not, if its not good practice using it in production site then please give me the suggestions for an alternate of GOTO.

How do you know the `goto` statement is the issue? It seems very unlikely the `goto` itself would cause an issue, more likely the code that gets run under a given branch. — Dale K, Mar 16 '20 at 09:40
I've executed it manually by removing the goto statements then it will be executed less time, It seems very unlikely the goto itself would cause an issue => don't know the if it is causing an issue or not because until now it will work fine when deployment but taking the time and will be increased along with marker :( — Hungry_Developer, Mar 16 '20 at 09:42
Your best bet is to inspect (and then post) the execution plan. — Dale K, Mar 16 '20 at 09:43
The way you have written it in the question, if the code jumps to `MARKER` then its also going to execute `MARKER2`, `MARKER2` and so on. is that really how your code looks? — Dale K, Mar 16 '20 at 09:44
There is nothing in the code you have shown which would cause a performance issue. Under normal circumstances GOTO will be very fast, its not like it has to do anything. So whatever is causing the performance issue is related to the code as a whole, and therefore we need to see the execution plan (which is an essential part of any performance question). — Dale K, Mar 16 '20 at 09:49
In terms of best practice see this [duplicate](https://stackoverflow.com/questions/3046017/sql-goto-statement) — Dale K, Mar 16 '20 at 09:51
[And another](https://stackoverflow.com/questions/34453903/understanding-t-sql-goto) — Dale K, Mar 16 '20 at 09:55
Thank you @DaleK, by your suggestions one thing will be clarified " GOTO " is worst coding and will be used if there is no alternative — Hungry_Developer, Mar 16 '20 at 10:44
Bad coding practice doesn't mean its slow. Anyway you have an answer below - have you tried it? — Dale K, Mar 16 '20 at 19:01

Sebastian Brosch · Answer 1 · 2020-03-16T10:25:48.380

1

You can replace the GOTO statements with a script like this using IF:

-- get the number of the marker (instead of the full name of the marker).
DECLARE @Marker INT = (SELECT REPLACE(Value, 'Marker', '') FROM Table1 WHERE name = 'Marker')

IF @Marker IS NULL
  BEGIN
    PRINT 'no marker'
  END
IF @Marker < 1
  BEGIN
    -- Code for Marker No. 1
  END
IF @Marker < 2
  BEGIN
    -- Code for Marker No. 2
  END
IF @Marker < 3
  BEGIN
    -- Code for Marker No. 3
  END
IF @Marker < 4
  BEGIN
    -- Code for Marker No. 4
  END
ELSE IF @Marker IS NOT NULL
  BEGIN
    PRINT 'no changes'
  END

demo on dbfiddle.uk

edited Mar 16 '20 at 10:25

answered Mar 16 '20 at 09:49

Sebastian Brosch

42,106
15
72
87

You’re missing a bunch of ELSE statements. If you assume that was the op’s intent, you should note that in the answer. – Sebastian Meine Mar 16 '20 at 11:56

Performance Issue due to GOTO statement

1 Answers1