Performance query issue

[Former Member]

Hi guys,

I need sales averages from a COPA table, the problem is that the query that reads the averages is in a loop for each employee that get comission from sales (500).

So the programs runs in 4 or 5 hours.

Here is the code.

loop at ti_pa0001.

SELECT SUM( erlos ) SUM( vv010 ) SUM( absmg )

INTO (vtas20, marg20, v_pza20)

FROM ce1pisa

WHERE vrgar = 'F'

AND perio IN r_periodo

AND plikz = '0'

AND bzirk IN r_zonav

AND kndnr IN r_cliente

AND kmmakl IN r_linea

AND kmvkgr IN r_territorio

AND vkorg IN r_vkorg " ogr de ventas.

AND bukrs IN r_sociedad

AND vtweg = '20'. "hospitales

endloop.

the filters are in range, because can change from employee to employee.

any ideas?

tks in advance

Edited by: javier santana on Jul 28, 2008 8:08 PM

[Former Member]

why do you have que query inside the loop if you don't use any field of the table in the "where" conditions?

[Former Member]

Actually the code is like

loop at ti_pa0001.

SELECT SUM( erlos ) SUM( vv010 ) SUM( absmg )

INTO (vtas20, marg20, v_pza20)

FROM ce1pisa

WHERE vrgar = 'F'

AND perio IN r_periodo

AND plikz = '0'

AND bzirk IN r_zonav

AND kndnr IN r_cliente

AND kmmakl IN r_linea

AND kmvkgr IN r_territorio

AND vkorg IN r_vkorg " ogr de ventas.

AND bukrs IN r_sociedad

AND vtweg = '20'. "hospitales

IF sy-subrc = 0.

sum_vtas20 = vtas20 + sum_vtas20.

sum_marg20 = marg20 + sum_marg20.

sum_vpza20 = v_pza20 + sum_vpza20.

ENDIF.

*NE 020

SELECT SUM( erlos ) SUM( vv010 ) SUM( absmg )

INTO (vtas, marg, v_pza)

FROM ce1pisa

• FOR ALL ENTRIES IN r_zonav

WHERE vrgar = 'F'

AND perio IN r_periodo

AND plikz = '0'

AND bzirk IN r_zonav

AND kndnr IN r_cliente

AND kmmakl IN r_linea

AND kmvkgr IN r_territorio

AND vkorg IN r_vkorg " ogr de ventas.

AND bukrs IN r_sociedad

AND vtweg NE '20'. " ne 20

IF sy-subrc = 0.

sum_vtas_ot = vtas + sum_vtas_ot.

sum_marg_ot = marg + sum_marg_ot.

sum_vpza_ot = v_pza + sum_vpza_ot.

ENDIF.

Endloop.

and the table is 480 000, and grows 40,000 each month.

Edited by: javier santana on Jul 28, 2008 8:20 PM

Edited by: javier santana on Jul 28, 2008 8:23 PM

[Former Member]

Try to avoid the select queries inside the loop.

1) First you select required field from the table

2) do sum calculation in the loop.

The performance will be high.

Regards

R.Rajendran

[Former Member]

I think you are doing the same query over and over again...

try something like this...

SELECT SUM( erlos ) SUM( vv010 ) SUM( absmg )
INTO corresponding fields if table t_sums  " save the result in a internal table
FROM ce1pisa
WHERE vrgar = 'F'
AND perio IN r_periodo
AND plikz = '0'
AND bzirk IN r_zonav
AND kndnr IN r_cliente
AND kmmakl IN r_linea
AND kmvkgr IN r_territorio
AND vkorg IN r_vkorg " ogr de ventas.
AND bukrs IN r_sociedad
AND vtweg = '20'. "hospitales

* and then,...

loop at ti_pa001.
* if you need the result sum...

read table t_sums where.. 
....

endloop.

[Former Member]

How many entries in ce1pisa?

Rob