Question Re: Faster loop? in Spend Management Q&A

Faster loop?

Former Member — Wed, 27 Nov 2019 10:31:17 GMT

Hello,

is there a way to make a loop faster or to replace it by something faster? I know of 'for all entries' but what if the two fields aren't compatible?

SELECT rqposname RQIDENT
INTO TABLE t_output
FROM tsp01
WHERE rqposname EQ lv_email.

LOOP AT t_output ASSIGNING FIELD-SYMBOL(<fs2>).
SELECT SINGLE jobname progname variant listident
INTO ( <fs2>-jobname, <fs2>-progname, <fs2>-variant, <fs2>-listident )
FROM tbtcp
WHERE listident EQ <fs2>-rqident
AND listident NE '0'.
ENDLOOP.

This takes a few minutes even though there are "only" 1300 rows in t_output.

Any Tips?

Thanks in advance.

Re: Faster loop?

former_member182371 — Wed, 27 Nov 2019 10:43:15 GMT

maybe a JOIN like in the thread below?

Select Join problem

Best regards,

Pablo

Re: Faster loop?

Sandra_Rossi — Wed, 27 Nov 2019 10:44:37 GMT

Did you try a join?

Re: Faster loop?

Tomas_Buryanek — Wed, 27 Nov 2019 10:51:03 GMT

SELECT inside LOOP is generally bad for performance! Always try to SELECT your data in one DB fetch if possible.

In this case I would suggest simple SELECT with JOIN of the two tables.

Re: Faster loop?

shakirali7 — Wed, 27 Nov 2019 11:26:50 GMT

Hi !

Do make a Database View joining both tables with your desire condition and then use it appropriatly in you programe.

Hope it helps,

best regards,

Shakir.

Re: Faster loop?

Former Member — Wed, 27 Nov 2019 11:52:56 GMT

Yes I did

but unfortunaly listident and rqident are not the same type.

Re: Faster loop?

Former Member — Wed, 27 Nov 2019 11:53:24 GMT

Yes I did try that

but unfortunaly listident and rqident are not the same type.

Re: Faster loop?

Former Member — Wed, 27 Nov 2019 11:53:44 GMT

Yes I did try that

but unfortunaly listident and rqident are not the same type.

Re: Faster loop?

matt — Wed, 27 Nov 2019 12:07:37 GMT

I know of 'for all entries' but what if the two fields aren't compatible?

Why not type the internal table so they are compatible?

Re: Faster loop?

Tomas_Buryanek — Wed, 27 Nov 2019 12:19:49 GMT

teaman I see, in this case you can either create a table for FOR ALL ENTRIES or range table. And you will have to retype the rqident to listident.

But now I see another and bigger problem - your both conditions (only the one you are using) are not index nor key fields:

TBTCP-LISTIDENT
TSP01-RQPOSNAME

Re: Faster loop?

ascm — Wed, 27 Nov 2019 12:58:06 GMT

Two CDS!

First:

define view ztbtcp 
  as select from tbtcp
{
  cast ( listident as abap.int4 ) as listident,
  variant,
  progname,
  jobname
}

Second:

define view zoutput
   as select from tsp01
    left outer join ztbtcp
    on tsp01.rqident = ztbtcp.listident 
{
rqposname,
listident,
variant,
progname,
jobname
}
where listident <> 0

If you need more fields in the output-structure, define them in the second CDS.

Re: Faster loop?

former_member182371 — Wed, 27 Nov 2019 13:03:53 GMT

Hi,

instead of TSP01(Spool Requests) ,

would TBTC_SPOOLID(Background Processing Spool IDs Table) be a valid table?

(check report BTCAUX05)

BEst regards,

Pablo

Re: Faster loop?

Sandra_Rossi — Wed, 27 Nov 2019 13:12:41 GMT

Andrea Clöß it's worth converting it into an answer!

Re: Faster loop?

Sandra_Rossi — Wed, 27 Nov 2019 13:23:36 GMT

This works for me (ABAP >= 7.50 - CAST of type INT4 or NUMC):

DATA lv_email TYPE tsp01-rqposname.
SELECT s~rqident,                                       " <========== INT4
       j~listident,                                     " <========== NUMC (10)
       s~rqposname, j~jobname, j~progname, j~variant
    FROM tsp01 AS s
        INNER JOIN tbtcp AS j
            ON CAST( j~listident AS INT4 ) = s~rqident
    WHERE s~rqposname = @lv_email
      AND j~listident <> '0000000000'
INTO TABLE @DATA(t_output).

Re: Faster loop?

former_member1716 — Wed, 27 Nov 2019 14:03:58 GMT

Hello Nils Bla,

if not CDS views, you can use the below code as a solution. This should work fine.

TYPES: BEGIN OF ty_tsp01,
         rqposname TYPE tsp01-rqposname,
         rqident   TYPE tbtcp-listident,
       END OF ty_tsp01.

DATA: lv_email TYPE tsp01-rqposname,
      ls_tsp01 TYPE ty_tsp01,
      lt_tsp01 TYPE STANDARD TABLE OF ty_tsp01.

SELECT rqposname, rqident
FROM tsp01
WHERE rqposname EQ @lv_email
INTO TABLE @DATA(t_tsp01).
IF sy-subrc EQ 0.
  SORT t_tsp01 BY rqident.
  LOOP AT t_tsp01 INTO DATA(ls_temp).
    CLEAR: ls_tsp01.
    ls_tsp01-rqposname = ls_temp-rqposname.
    ls_tsp01-rqident   = ls_temp-rqident.
    APPEND ls_tsp01 TO lt_tsp01.
  ENDLOOP.
ENDIF.

SELECT jobname,
       progname,
       variant,
       listident
FROM tbtcp
FOR ALL ENTRIES IN @lt_tsp01
WHERE listident = @lt_tsp01-rqident AND
      listident NE 0
INTO TABLE @DATA(t_tbtcp).
IF sy-subrc EQ 0.
  SORT t_tbtcp BY listident.
ENDIF.

Regards!

Re: Faster loop?

Nawanandana — Wed, 27 Nov 2019 15:44:28 GMT

Try to define your internal table as hashed table. try to do same, it will be fast

DATA IT_TAB TYPE HASHED TABLE OF scarr
WITH UNIQUE KEY carrid.

Re: Faster loop?

Former Member — Wed, 04 Dec 2019 10:37:46 GMT

I've just moved out my selects with an "for all entrys" that made it faster.