SAP HANA XSJOB is running too long time ( 15-16 hours)

[Former Member]

I have create stored procedures and running on(HANA System) some jobs.
This jobs(xsjob) It was running short while working on the old system.
But now , we have new HANA system and while in the new system this jobs running very long(15-16 hours).

*I am trying simple stored procedure and running short time in new system , but when i run complex and long SQL i am wait so time.

How can we reduce this running time ?

Thanks for your answers.
Yunus SAHİN

[Former Member]

Hi Lucas,

I did not say anything to imply anything, but I just wanted to say that I have been working on these subjects before.
Why is there a feeling of sensitivity or why are you so surprised? I think we dont need it.

Anykow,
XSJS,

function MeterReading(){

var sql = "call \"_SYS_BIC\".\"XS/METER\"";

var conn = $.db.getConnection();

var pstmt = conn.prepareStatement(sql);

pstmt.execute();

conn.commit();

conn.close();

}

XSJOB,

JB_METER{

"description": "Read Meter Reading Document",

"action": "XS:MeterReading.xsjs::MeterReading",

"schedules": [

{

"description": "Read Meter Reading Document",

"xscron": "* * * * * 54 0"

}

]

}

STORED PROCEDURE

CREATE PROCEDURE METER ( )

LANGUAGE SQL SCRIPT

SQLSECURITY INVOKER

DEFAULT SCHEMA XXXX AS

BEGIN

DROP TABLE METER;

CREATE COLUMN TABLE METER AS (

SELECT
       'HEADER' as TABLE,
         h.EQUNR,
         h.ADATE,
         h.ATIME,
         h.ADATSOLL,
         h.ABLESER,
         h.ANLAGE,
         h.TERMSCHL,
         h.FLG10,
         h.FLG08,
         h.USRID,
         h.MRONO,
         1 as Sayi,
         h.FLG14,
         h.FLGMN
    
    
         FROM ZHH_MR_HDR h
                   
INNER JOIN  "_SYS_BI"."M_TIME_DIMENSION" m on h.ADATE=m.DATE_SAP 
          WHERE  ( m.DATE_SQL between ADD_DAYS(CURRENT_DATE,-EXTRACT(DAY FROM CURRENT_DATE) + 1) and LAST_DAY(CURRENT_DATE)) 
            
AND h.FLG08<>''
         );
       
 CREATE LOCAL TEMPORARY TABLE #TEMP_METER AS (
select l.ABLBELNR,l.EQUNR,l.ADAT,l.ATIM,l.ABLESER,l.AKTIV,l.ABLSTAT from  EABL l
                                             
       INNER JOIN  "_SYS_BI"."M_TIME_DIMENSION" m on l.ADAT=m.DATE_SAP 
                                               
       where ( m.DATE_SQL between ADD_DAYS(CURRENT_DATE,-EXTRACT(DAY FROM CURRENT_DATE) + 1) and LAST_DAY(CURRENT_DATE)) 

                                                     
AND l.ABLSTAT='1'
);

CREATE LOCAL TEMPORARY TABLE #TEMP_METER2 AS (
SELECT * FROM #TEMP_METER 
WHERE  NOT EXISTS(SELECT 1 FROM ZHH_MR_ITM t WHERE #TEMP_METER.ABLBELNR=t.ABLBELNR ) 
) ;      

INSERT INTO M_FCT_METER_SUM
   SELECT 
        DISTINCT
        'EABL',
          l.EQUNR,
          l.ADAT,
          l.ATIM,
          g.ADATSOLL,
          l.ABLESER,
          g.ANLAGE,
          g.ABLEINH,
          case when l.ABLSTAT='1' and l.AKTIV='1' then 'X' else '' end,
          case when l.ABLSTAT='1' and l.AKTIV='0' then 'X' else '' end,
          '',
          '',
          1,
          case when exists ( select 1 
                                                 
from EANLH h                                 
where h.ANLAGE=g.ANLAGE and h.AKLASSE='0002'                                
and ( l.ADAT between h.AB and h.BIS) ) then '' else 'X' end,
       'Y'                      
FROM #TEMP_METER2 l                     
INNER JOIN EABLG g on l.ABLBELNR=g.ABLBELNR                   
WHERE g.ABLESGR='01';

DROP TABLE #TEMP_METER;
DROP TABLE #TEMP_METER2;<br>END;

.xsaccess
{

"exposed" : true,

"authentication" :

{

"method": "Form"

},

"cache_control" : "must-revalidate",

"cors" :

{

"enabled" : false

},

"enable_etags" : false,

"force_ssl" : false,

"prevent_xsrf" : true

}

They are basically there. We can continue here. I am trying on SQL script editor and runtime is so short than(1-2 minute) xsjob.
Maybe we need change XSJOB directly the Stored Procedure , maybe dont need xsjs.

I ran the code piece by piece. Where the code is blocked when CREATE LOCAL TEMPORARY TABLE #TEMP_METER2 AS After the code snippet.
I try de piece stored procedure and run piece xsJob. İ am error get after CREATE LOCAL TEMP..........
but before this code running succesfully (33 sec).

Thank you for answer.

Yunus

[lucas_oliveira]

Hi,

You're not really helping us to help you with this scenario. No xscronjob definition, no frequency explained, no xsjs details, no procedure code, etc ...

..but hey, I'm gonna try helping anyway. First thing (and the most important!) is to know what exactly is causing your job to run forever. So far this is not clear from your description.

Anyhow: I'm guessing you're using a xsjs service to call the procedure. If that's the case, why don't you try calling that service directly from an http call and verify if you can reproduce the issue.

If that's the case, then I'd assume you are either having a performance issue on your xsjs service (long loops, creating some crazy js string, dumping a huge file, etc) or the procedure call you're using on the job is not the same as the one you executed on SQL Editor (different parameter values or even different procedure for example).

A good option in those cases is to debug the xsjs and try to spot exactly where the time is being spent (or getting stuck). That will also allow you to check what parameters are fed to the procedure.

Now, enough with the guesswork. With (much) more details we can try to help further.

Ps.:

"[...]Also I am a normal level(little) experienced before about XSjob,Partition or Stored Procedure.[...]" 

Uh? What do you mean by that?

BRs,

Lucas de Oliveira

[Former Member]

Hi Lucas ,

thank you for answering.

New system copy from old system ( technical features 1 to 1 ).we dont change anything insomuch that data volume less than old system.
Also I am a normal level(little) experienced before about XSjob,Partition or Stored Procedure. Because i am use intense on old Hana System and SAP BO systems.
More detailed , my SQL is not so specific i am use 3 system table and 2 temp table . When i run Stored Procedure on SQL editor this passing time 1-2 minute maybe less than minutes.
XSjob error image inserted.capture.png

Yunus

[lucas_oliveira]

Hi Yunus,

You're not being specific here. However: does running the complex-and-long SQL on HANA (SQL Editor) also hangs? If so, you need to evaluate the performance of your SQL first. Performance analysis tips and tricks are all around the web and on SAP marketplace. A starter could be the SAP HANA Troubleshooting and Performance Analysis Guide.

If that's not a problem, then please be more specific in regards to your issue: whats the frequency of your procedure? whats the data volume difference between the systems you're comparing? does the procedure runs fine on SQL editor? etc. The more you describe what you have done to troubleshoot your scenario the more we can try helping.

Regards,

Lucas de Oliveira