How to stop commands that are taking too long to respond?

[former_member198127]

I am trying to write some stored procedure having loops, and it is often taking long time to respond. I am often not even aware that at what part of loop they are currently running in? i wanted to track the values of variables to check the intermediate values.

How can i stop this execution in middle to track the values of variables?

Is there any other way i can control the execution of commands taking long time?

[lbreddemann · Accepted solution]

Hey Vishal,

to track the current loop state in running SQLScript variables you could use the session context variables, as I explained in http://scn.sap.com/message/13754597#13754597

Be a bit careful on how often you update your variable so that you don't waste too much time with that.

To stop the execution, you either would have to cancel the statement (via client coding) or you'd have to cancel the command via http://help.sap.com/hana/html/sql_alter_system_cancel_work.html.

Cheers, Lars

[mtremblay-savard]

Hi Vishal,

You can make some messages before and after your stored procedures. You can also call the GET RUN TIME FIELD statement to know how much time your stored procedure too.

Here is an example :
GET RUN TIME FIELD t0.
stored procedure
GET RUN TIME FIELD rt.

rt = rt - t0.

msg = rt.

MESSAGE msg TYPE 'I'.

This way you will know how long each of your procedure took, you can even concatenate the name of the procedure into the message for more precision.

Best Regards,
Max

[former_member198127]

Hello Maxime,

I tried using your suggested statement GET RUN TIME FIELD, but it was giving an error:

Could not execute 'create procedure PRACTICE_TEST.PROC_PUZZLE_OUTPUT LANGUAGE SQLSCRIPT as ELEPHANT_COUNT integer; ...'

SAP DBTech JDBC: [257] (at 250): sql syntax error: incorrect syntax near "RUN": line 15 col 7 (at pos 250)

Also when i tried searching for details of this command, i couldn't find anything. Can yo uplease provide some details about usage of this command.

[mtremblay-savard]

Hi Vishal,

The get run time statement must be before and after your select or stored procedure. Can you send me the part of code that does not execute?

Here is some info about the get run time statement :
http://help.sap.com/saphelp_nw04/helpdata/en/9f/db994235c111d1829f0000e829fbfe/content.htm
Or maybe it might be more useful for you to use GET TIME instead :
http://scn.sap.com/thread/1524652

Regards,
Max

[former_member198127]

Hey Maxime,

Here is the procedure for which i am trying to identify the total duration of run.

create procedure PRACTICE_TEST.PROC_PUZZLE_OUTPUT
LANGUAGE SQLSCRIPT
as
  ELEPHANT_COUNT  integer;
  COW_COUNT  integer;
  GOAT_COUNT  integer;
  ELEPHANT  integer;
  CNTR integer;
  v_msg varchar(100);
  t0 time;
  t1 time;
  rt time;

Begin
  GET RUN TIME FIELD t0;
  ELEPHANT_COUNT := 1; 
  CNTR := 0;
  v_msg := '';
  While ELEPHANT_COUNT < 10
    DO
    COW_COUNT := 1;
      While COW_COUNT < 100
        DO
          GOAT_COUNT := 1;
          while GOAT_COUNT < 100
            Do
              CNTR := CNTR + 1;
               if MOD(:cntr, 1000) = 0 then
                 v_msg := 'CNTR RUN COUNT = '||:CNTR;
                 call PRACTICE_TEST.PROC_MY_LOG(:v_msg);
               end if;
               if ((:ELEPHANT_COUNT + :COW_COUNT + :GOAT_COUNT) = 100) AND ((:ELEPHANT_COUNT * 10) + (:COW_COUNT * 3)  + (:GOAT_COUNT * 0.5) = 100)
      THEN
                   v_msg := 'CNTR RUN COUNT = '||:CNTR || 'Goat Loop : Elephant count = '||:ELEPHANT_COUNT||' Cow count = '||:COW_COUNT||'Goat count = '||:GOAT_COUNT;
                   call PRACTICE_TEST.PROC_MY_LOG(:v_msg);
               end if;
               GOAT_COUNT := :GOAT_COUNT + 1;
          END While;
         COW_COUNT := :COW_COUNT + 1;
      End While;
   ELEPHANT_COUNT := :ELEPHANT_COUNT + 1;
  end while;
  SELECT CURRENT_TIME FROM DUMMY;
  GET RUN TIME FIELD t1;
  rt := :t1 - :t0;
  v_msg := 'TOTAL TIME TAKEN IS '||:rt;
  call PRACTICE_TEST.PROC_MY_LOG(:v_msg);
End;

[former_member184768]

Hi Vishal,

I haven't used GET RUN TIME FIELD so far, but use a rough method to calculate my procedure run time..

It uses the proc_my_log procedure which inserts a row in a log table.

create procedure t_dummy language sqlscript as

v1 timestamp;

v2 timestamp;

v3 int;

begin

select now() into v1 from dummy;

call proc_my_log(to_varchar(:v1));

/*

  long running SQL statements, Procedure code

*/

select now() into v2 from dummy;

call proc_my_log(to_varchar(:v2));

select seconds_between(:v1, :v2) into v3 from dummy;

call proc_my_log(to_varchar(:v3));

end;

Then I fire a select from the log table to check when the procedure started, when it ended and what is the execution duration in seconds.

You can modify the procedure as per your requirement.

Just trying to keep things simple..

Regards,

Ravi

[lbreddemann]

Hmm... instrumentation is a good idea, when you assume that the steps in your program are executed in the exact order you wrote them.

But this is pretty much not the case for SQLScript - in fact, a major feature of SQLScript is that it allows for automatic re-ordering and parallel execution of commands where doing just that is safe.

Therefore, you might end up with not so useful information here.

A much easier and better approach is to use the plan visualization (see e.g. here: or the current documentation).

- Lars

[former_member198127]

Hey Ravindra,

This is really nice way to track the duration.

Thanks.