topic Performance query issue in Application Development and Automation Discussions

Performance query issue

Former Member — Mon, 28 Jul 2008 18:07:08 GMT

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

Re: Performance query issue

Former Member — Mon, 28 Jul 2008 18:15:50 GMT

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

Re: Performance query issue

Former Member — Mon, 28 Jul 2008 18:16:58 GMT

How many entries in ce1pisa?

Rob

Re: Performance query issue

Former Member — Mon, 28 Jul 2008 18:20:28 GMT

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

Re: Performance query issue

Former Member — Mon, 28 Jul 2008 18:25:51 GMT

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

Re: Performance query issue

Former Member — Mon, 28 Jul 2008 18:26:43 GMT

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.