Skip to Content
0
Sep 09, 2010 at 11:04 AM

strange behavior for SELECT with TIME_OUT

64 Views

Hi all,

I have a report that executes in 10 secs or throws short dump with TIME_OUT (Time limit exceeded.) depending on just one (select-options) parameter.

For example:

1. if i choose "CTRFND1" the report throws short dump with TIME_OUT.

2. if i choose "CTRFND1" and exclude value "DUMMY" the report goes in 10 secs. all is perfect.

This was yesterday.

Today i get short dump for both cases and i tried with "CTRFND1" and exclude "DUMMY" and "DUMMY" again, and the report goes in 10 secs.

Report gets stuck at select from table FMIFIIT. Which has rows only with "CTRFND1" values (so no rows with "DUMMY").

Why this behavior? I'm totally lost.

This is the parameter i change::

SELECT-OPTIONS s_CtrFnd FOR fmfctr-fictr obligatory.

This is the SELECT:

SELECT FMIFIIT~FIPEX sum( FMIFIIT~FKBTR ) as FKBTR FMIFIIT~PERIO FMIFIIT~WRTTP
      FROM FMIFIIT
      INNER JOIN FMCI ON FMCI~FIPEX = FMIFIIT~FIPEX
      INTO CORRESPONDING FIELDS OF TABLE tbl_FMIFIIT
      WHERE FMIFIIT~FIKRS = p_areaFM 
           AND FMIFIIT~GJAHR = p_year 
           AND FMIFIIT~FONDS = p_Fnd 
           AND FMIFIIT~FISTL IN s_ctrfnd 
           AND FMIFIIT~PERIO <= p_perio 
           AND ( ( FMIFIIT~WRTTP = '54' OR FMIFIIT~WRTTP = '66' ) 
           AND FMIFIIT~HKONT LIKE '6%' 
           AND FMIFIIT~BTART = '0100' 
            OR FMIFIIT~WRTTP = '57' AND FMIFIIT~BTART = '0250'  
            OR FMIFIIT~WRTTP = '61' AND FMIFIIT~BTART = '0100' ) 
           AND FMCI~POTYP = '3' AND FMCI~FIKRS = p_areaFM
      GROUP BY FMIFIIT~FIPEX FMIFIIT~PERIO FMIFIIT~WRTTP FMCI~POTYP.

Maybe the select statement is not great but this shouldn't make this big difference of time execution. When I run the report in background it goes in more than 1000 secs (for time_out parameters in foreground).

I want to point out that s_ctrfnd is the only parameter i change.

Did anyone encountered such behavior? Any idea how to avoid this?

Thank you,

Adi

Edited by: Rob Burbank on Sep 9, 2010 9:35 AM