| View previous topic :: View next topic |
| Author |
Message |
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
 Posted: Thu Nov 18, 2010 5:53 pm Post subject: Большая выборка в цикле происходит замедление программы |
 |
|
Добрый день!
Посоветуйте что можно сделать очень большая выборка, цикл по месяцам внутри запрос и фм скаждым месяцем все медленее и медленее. |
|
| Back to top |
|
 |
Dmitriy
Аналитик
Age: 46
Joined: 14 Nov 2008
Posts: 300
Location: Russia
|
| Posted: Thu Nov 18, 2010 8:18 pm Post subject: |
|
|
Для начала можно порекомендовать ознакомиться с некоторыми рекомендациями здесь + курс. Если использование приведённых по ссылке приёмов/примеров не приведёт к заметному улучшению производительности выборки и обработки данных, то приводИте код, посмотрим.
_________________
ABAP/4 You |
|
| Back to top |
|
|
vga
Мастер
Age: 190
Joined: 04 Oct 2007
Posts: 1218
Location: Санкт-Петербург
|
| Posted: Fri Nov 19, 2010 8:01 am Post subject: |
|
|
Для начала покажите вашу выборку, иначе конкретного ответа не получите.
_________________
Молитва - это запрос разработчику на изменение кода программы. |
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 11:26 am Post subject: |
|
|
| Code: |
LOOP AT dat_tb. "20100501|20100531 итд.
CLEAR: pr_1, pr_2.
pr_2 = LINES( tab_pernr ).
LOOP AT tab_pernr. " табельные номера приблизительно до 20 000
CONCATENATE dat_tb-low+4(2) '.' dat_tb-low(4) INTO pr_mm.
ADD 1 TO pr_1.
SUBTRACT 1 FROM pr_2.
mp = pr_1 MOD 100.
IF mp EQ 0.
CALL FUNCTION 'HR_PCLX_INIT_BUFFER'. " LAT
CONCATENATE 'Період: ' pr_mm ' [ ' ' Оброблено таб: ' pr_1 'Залишилось таб: ' pr_2 ' ]' INTO na SEPARATED BY space.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = na.
COmmit WORK.
ENDIF.
PERFORM zhr_func_sum_nr TABLES tab_orgeh USING tab_pernr-pernr dat_tb-low dat_tb-high.
PERFORM raspr_sum.
FREE t_lgart_sume.
FREE db_orgeh_error.
CLEAR : t_lgart_sume,db_orgeh_error.
ENDLOOP.
ENDLOOP.
|
|
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 11:28 am Post subject: |
|
|
| Code: | FORM zhr_func_sum_nr TABLES tab_orgeh TYPE tab_objid_authority_
USING i_pernr LIKE pa0001-pernr
per_begda LIKE sy-datum
per_endda LIKE sy-datum .
CLEAR : p_bukrs, p_orgeh, p_persk, p_persg, p_werks, p_btrtl, vinfo, ver.
REFRESH: wpbp, wtc_lgart_spl.
CALL FUNCTION 'ZHR_UA_READ_PYRES_INTERVAL'
EXPORTING
pernr = i_pernr
mode = 'I'
begda = per_begda
endda = per_endda
tpwtc = 'Z'
wpbpr = 'X'
repid = '3102'
tpwtc_spl = 'X'
TABLES
wpbp = wpbp
wtc_lgart_spl = wtc_lgart_spl.
CHECK ( sy-subrc = 0 ).
SORT wpbp BY apznr endda.
SORT wtc_lgart_spl BY apznr per.
LOOP AT wtc_lgart_spl.
CLEAR: p_bukrs, p_persk,p_persg,p_btrtl, p_werks, p_orgeh, t_lgart_sume, ver.
t_lgart_sume-pernr = i_pernr.
CASE wtc_lgart_spl-wcode.
**********************************************************************
WHEN 'Z3102004'.
CLEAR ver.
IF wtc_lgart_spl-apznr <> 0.
LOOP AT wpbp WHERE apznr = wtc_lgart_spl-apznr AND endda(6) = wtc_lgart_spl-per .
PERFORM dop_obr_uvol.
ENDLOOP.
ELSE.
LOOP AT wpbp WHERE endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
LOOP AT wpbp WHERE endda(6) = per_endda(6) .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
CLEAR ver.
IF per_endda(4) = enddt(4).
IF wtc_lgart_spl-per <= per_endda(6) AND wtc_lgart_spl-per(4) = enddt(4) .
t_lgart_sume-per = per_endda+4(2).
ver = 'X'.
ELSEIF wtc_lgart_spl-per > per_endda(6) AND wtc_lgart_spl-per(4) = enddt(4) .
t_lgart_sume-per = wtc_lgart_spl-per+4(2).
ver = 'X'.
ELSEIF wtc_lgart_spl-per(4) < enddt(4) .
t_lgart_sume-per = per_endda+4(2).
ver = 'X'.
ENDIF.
ELSE.
IF wtc_lgart_spl-per(4) = enddt(4) .
t_lgart_sume-per = wtc_lgart_spl-per+4(2).
ver = 'X'.
ENDIF.
ENDIF.
IF ver = 'X'.
t_lgart_sume-orgeh = p_orgeh.
t_lgart_sume-werks = p_werks.
t_lgart_sume-persk = p_persk.
t_lgart_sume-persg = p_persg.
t_lgart_sume-btrtl = p_btrtl.
t_lgart_sume-pernr = i_pernr.
t_lgart_sume-sum = wtc_lgart_spl-sum.
t_lgart_sume-per_sum = wtc_lgart_spl-per.
t_lgart_sume-wcode = wtc_lgart_spl-wcode.
t_lgart_sume-lgart = wtc_lgart_spl-lgart.
t_lgart_sume-fm_per = per_begda(6).
PERFORM verifi TABLES tab_orgeh USING i_pernr CHANGING ver.
IF ver = 'X'.
t_lgart_sume-status = cstatus-report.
APPEND t_lgart_sume.
ELSEIF ver IS INITIAL.
APPEND t_lgart_sume TO db_orgeh_error.
ENDIF.
ENDIF.
CLEAR t_lgart_sume.
CLEAR db_orgeh_error.
**********************************************************************
WHEN 'Z3102014'.
CLEAR: vinfo,ver.
CONCATENATE wtc_lgart_spl-vinfo+2(4) wtc_lgart_spl-vinfo(2) INTO vinfo.
IF vinfo >= wtc_lgart_spl-per .
IF wtc_lgart_spl-apznr <> 0.
LOOP AT wpbp WHERE apznr = wtc_lgart_spl-apznr AND endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ELSE.
LOOP AT wpbp WHERE endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
ELSE.
LOOP AT wpbp WHERE endda(6) = vinfo .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
LOOP AT wpbp WHERE endda(6) = per_endda(6) .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
IF wpbp-massn = 'Z0' OR wpbp-massn = 'R1' OR wpbp-massn = 'P3'.
PERFORM vinfo_0001 USING i_pernr vinfo CHANGING p_orgeh p_persk p_persg p_werks p_bukrs.
ENDIF.
ENDIF.
CLEAR ver.
IF per_endda(4) = enddt(4).
IF vinfo <= per_endda(6) AND vinfo(4) = enddt(4) .
t_lgart_sume-per = per_endda+4(2).
ver = 'X'.
ELSEIF vinfo > per_endda(6) AND vinfo(4) = enddt(4) .
t_lgart_sume-per = vinfo+4(2).
ver = 'X'.
ELSEIF vinfo(4) < enddt(4) .
t_lgart_sume-per = per_endda+4(2).
ver = 'X'.
ENDIF.
ELSE.
IF vinfo(4) = enddt(4) .
t_lgart_sume-per = vinfo+4(2).
ver = 'X'.
ENDIF.
ENDIF.
IF ver = 'X'.
t_lgart_sume-orgeh = p_orgeh.
t_lgart_sume-werks = p_werks.
t_lgart_sume-persk = p_persk.
t_lgart_sume-persg = p_persg.
t_lgart_sume-btrtl = p_btrtl.
t_lgart_sume-pernr = i_pernr.
t_lgart_sume-sum = wtc_lgart_spl-sum.
t_lgart_sume-per_sum = vinfo.
t_lgart_sume-wcode = wtc_lgart_spl-wcode.
t_lgart_sume-lgart = wtc_lgart_spl-lgart.
t_lgart_sume-fm_per = per_begda(6).
CLEAR ver.
PERFORM verifi TABLES tab_orgeh USING i_pernr CHANGING ver.
IF ver = 'X'.
t_lgart_sume-status = cstatus-report.
APPEND t_lgart_sume.
ELSEIF ver IS INITIAL.
APPEND t_lgart_sume TO db_orgeh_error.
ENDIF.
" ENDIF.
ENDIF.
CLEAR t_lgart_sume.
CLEAR db_orgeh_error.
**********************************************************************
WHEN 'Z3102001' OR 'Z3102002' OR 'Z3102003' OR 'Z3102005'. " Берем только для текущего года Без анализа периода
CLEAR ver.
IF per_endda(4) = enddt(4).
IF wtc_lgart_spl-apznr <> 0.
LOOP AT wpbp WHERE apznr = wtc_lgart_spl-apznr AND endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ELSE.
LOOP AT wpbp WHERE endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
LOOP AT wpbp WHERE endda(6) = per_endda(6) .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
t_lgart_sume-orgeh = p_orgeh.
t_lgart_sume-werks = p_werks.
t_lgart_sume-persk = p_persk.
t_lgart_sume-persg = p_persg.
t_lgart_sume-btrtl = p_btrtl.
t_lgart_sume-pernr = i_pernr.
t_lgart_sume-per = per_endda+4(2).
t_lgart_sume-per_sum = wtc_lgart_spl-per.
t_lgart_sume-sum = wtc_lgart_spl-sum.
t_lgart_sume-wcode = wtc_lgart_spl-wcode.
t_lgart_sume-lgart = wtc_lgart_spl-lgart.
t_lgart_sume-fm_per = per_begda(6).
"IF p_bukrs = BUKRS.
PERFORM verifi TABLES tab_orgeh USING i_pernr CHANGING ver.
IF ver = 'X'.
t_lgart_sume-status = cstatus-report.
APPEND t_lgart_sume.
ELSEIF ver IS INITIAL.
APPEND t_lgart_sume TO db_orgeh_error.
ENDIF.
ENDIF.
CLEAR t_lgart_sume.
CLEAR db_orgeh_error.
WHEN 'Z3102011' OR 'Z3102012' OR 'Z3102013' OR 'Z3102015'. " Берем только для текущего года Без анализа периода
CLEAR: vinfo, ver.
CONCATENATE wtc_lgart_spl-vinfo+2(4) wtc_lgart_spl-vinfo(2) INTO vinfo.
IF per_endda(4) = enddt(4).
IF vinfo >= wtc_lgart_spl-per .
IF wtc_lgart_spl-apznr <> 0.
LOOP AT wpbp WHERE apznr = wtc_lgart_spl-apznr AND endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ELSE.
LOOP AT wpbp WHERE endda(6) = wtc_lgart_spl-per .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
ELSE.
LOOP AT wpbp WHERE endda(6) = vinfo .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
LOOP AT wpbp WHERE endda(6) = per_endda(6) .
IF wpbp-orgeh <> 0.
PERFORM dop_obr_uvol.
ENDIF.
ENDLOOP.
ENDIF.
IF ver IS INITIAL .
IF wpbp-massn = 'Z0' OR wpbp-massn = 'R1' OR wpbp-massn = 'P3'.
PERFORM vinfo_0001 USING i_pernr vinfo CHANGING p_orgeh p_persk p_persg p_werks p_bukrs.
ENDIF.
ENDIF.
t_lgart_sume-orgeh = p_orgeh.
t_lgart_sume-werks = p_werks.
t_lgart_sume-persk = p_persk.
t_lgart_sume-persg = p_persg.
t_lgart_sume-pernr = i_pernr.
t_lgart_sume-btrtl = p_btrtl.
t_lgart_sume-per = per_endda+4(2).
t_lgart_sume-per_sum = vinfo.
t_lgart_sume-sum = wtc_lgart_spl-sum.
t_lgart_sume-wcode = wtc_lgart_spl-wcode.
t_lgart_sume-lgart = wtc_lgart_spl-lgart.
t_lgart_sume-fm_per = per_begda(6).
"IF p_bukrs = BUKRS.
CLEAR ver.
PERFORM verifi TABLES tab_orgeh USING i_pernr CHANGING ver.
IF ver = 'X'.
t_lgart_sume-status = cstatus-report.
APPEND t_lgart_sume.
ELSEIF ver IS INITIAL.
APPEND t_lgart_sume TO db_orgeh_error.
ENDIF.
ENDIF.
CLEAR t_lgart_sume.
CLEAR db_orgeh_error.
ENDCASE.
ENDLOOP.
ENDFORM. " |
|
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 11:31 am Post subject: |
|
|
По SE30 - 100% нагрузки дает ФМ
| Code: | REFRESH: wpbp, wtc_lgart_spl.
CALL FUNCTION 'ZHR_UA_READ_PYRES_INTERVAL'
EXPORTING
pernr = i_pernr
mode = 'I'
begda = per_begda
endda = per_endda
tpwtc = 'Z'
wpbpr = 'X'
repid = '3102'
tpwtc_spl = 'X'
TABLES
wpbp = wpbp
wtc_lgart_spl = wtc_lgart_spl. |
|
|
| Back to top |
|
|
Fami4
Специалист
Age: 42
Joined: 04 Aug 2009
Posts: 62
Location: UA
|
| Posted: Fri Nov 19, 2010 11:41 am Post subject: |
|
|
| msgim wrote: | По SE30 - 100% нагрузки дает ФМ
| Code: | REFRESH: wpbp, wtc_lgart_spl.
CALL FUNCTION 'ZHR_UA_READ_PYRES_INTERVAL'
EXPORTING
pernr = i_pernr
mode = 'I'
begda = per_begda
endda = per_endda
tpwtc = 'Z'
wpbpr = 'X'
repid = '3102'
tpwtc_spl = 'X'
TABLES
wpbp = wpbp
wtc_lgart_spl = wtc_lgart_spl. |
|
какие изменения вы вносили в ФМ? |
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 11:45 am Post subject: |
|
|
| Fami4 wrote: |
какие изменения вы вносили в ФМ? |
Изменения в ФМ вносил только консультант! |
|
| Back to top |
|
|
Удав
Гуру
Age: 48
Joined: 25 Jan 2008
Posts: 580
Location: Москва
|
| Posted: Fri Nov 19, 2010 2:13 pm Post subject: |
|
|
Можно попробовать запуск ФМ в параллельном режиме через STARTING NEW TASK и получения результата с помощью RECEIVE RESULTS
| Code: |
CLEAR: started, ended.
CALL FUNCTION 'SPBT_INITIALIZE'.
CALL FUNCTION ...
STARTING NEW TASK 'NAME'
PERFORMING get_result ON END OF TASK
if sy-subrc = 0.
started = 1.
endif.
WAIT UNTIL ended >= started.
...
FORM get_result USING taskname.
CHECK taskname = 'NAME'.
RECEIVE RESULT FROM ...
TABLES ...
ADD 1 TO ended.
ENDFORM.
|
_________________
С уважением,
Удав. |
|
| Back to top |
|
|
Dmitriy
Аналитик
Age: 46
Joined: 14 Nov 2008
Posts: 300
Location: Russia
|
| Posted: Fri Nov 19, 2010 3:37 pm Post subject: |
|
|
| msgim wrote: | | Fami4 wrote: |
какие изменения вы вносили в ФМ? |
Изменения в ФМ вносил только консультант! |
Можно код ФМ?
_________________
ABAP/4 You |
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 4:05 pm Post subject: |
|
|
Дамп: "The function module "ZHR_UA_READ_PYRES_INTERVAL" not released for 'remote'.
| Code: | CLEAR : started, ended.
CALL FUNCTION 'SPBT_INITIALIZE'.
CALL FUNCTION 'ZHR_UA_READ_PYRES_INTERVAL' STARTING NEW TASK 'SPROCESS'
PERFORMING get_result ON END OF TASK
EXPORTING
pernr = i_pernr
mode = 'I'
begda = per_begda
endda = per_endda
tpwtc = 'Z'
wpbpr = 'X'
repid = '3102'
tpwtc_spl = 'X'
.
if sy-subrc = 0.
started = 1.
endif.
WAIT UNTIL ended >= started. |
| Code: | FORM get_result USING taskname.
CHECK taskname = 'SPROCESS'.
RECEIVE RESULTS FROM FUNCTION 'ZHR_UA_READ_PYRES_INTERVAL'
TABLES
wpbp = wpbp
wtc_lgart_spl = wtc_lgart_spl.
ADD 1 TO ended.
ENDFORM. |
|
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 4:06 pm Post subject: |
|
|
| Или может я что неправильно написал? |
|
| Back to top |
|
|
Armann
Модератор
Joined: 01 Jan 2008
Posts: 422
Location: Moscow
|
| Posted: Fri Nov 19, 2010 4:30 pm Post subject: |
|
|
| msgim wrote: | | Или может я что неправильно написал? |
ФМ 'ZHR_UA_READ_PYRES_INTERVAL' должен быть определен как 'Дистанционный' (в свойствах ФМа галочка) |
|
| Back to top |
|
|
Удав
Гуру
Age: 48
Joined: 25 Jan 2008
Posts: 580
Location: Москва
|
| Posted: Fri Nov 19, 2010 4:41 pm Post subject: |
|
|
И для всех параметров ФМ должна быть поставлена галочка "Перенос по значению"
_________________
С уважением,
Удав. |
|
| Back to top |
|
|
msgim
Участник
Age: 39
Joined: 09 Sep 2009
Posts: 30
|
| Posted: Fri Nov 19, 2010 5:24 pm Post subject: |
|
|
| Armann wrote: | | msgim wrote: | | Или может я что неправильно написал? |
ФМ 'ZHR_UA_READ_PYRES_INTERVAL' должен быть определен как 'Дистанционный' (в свойствах ФМа галочка) |
Тип PUAPY_RUNS_T не известен или не разрешен
№ сообщения FL385
Немогу активировать когда ставлю галочку |
|
| Back to top |
|
|
|
