Posted: Wed Nov 05, 2008 12:10 pm Post subject: Тормозит селект по MSEG
Здравствуйте. Очень долго работет селект. Можно его оптимизировать?
Code:
DATA: BEGIN OF table_temp OCCURS 0,
mblnr LIKE mseg-mblnr,
budat LIKE mkpf-budat,
charg LIKE mseg-charg,
is_return LIKE mseg-smbln,
END OF table_temp.
SELECT mk~budat m1~mblnr
m1~charg m2~mblnr AS is_return
INTO CORRESPONDING FIELDS OF TABLE table_temp
FROM mseg AS m1
INNER JOIN mkpf AS mk ON mk~mblnr = m1~mblnr AND
mk~mjahr = m1~mjahr
LEFT JOIN mseg AS m2 ON
( m2~smbln = m1~mblnr AND
m2~sjahr = m1~mjahr AND
m2~smblp = m1~zeile AND
m2~bwart = '102' )
WHERE ( m1~ebeln = t_data-ebeln AND
m1~ebelp = t_data-ebelp AND
m1~bwart IN ('101', '103', '105') ).
Age: 85 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Wed Nov 05, 2008 1:41 pm Post subject:
Если в вашей системе существует индекс MSEG~S по полям smbln, sjahr, smblp, то торможение связано с использованием OUTER JOIN, для выполнения которого почти всегда (а может и всегда ) создается tmp таблица в базе.
Имхо, в данном случае, его лучше заменить набором селектов по полному ключу.
Code:
DATA: BEGIN OF table_temp OCCURS 0,
mblnr LIKE mseg-mblnr,
mjahr LIKE mseg-mjahr,
zeile LIKE mseg-zeile,
budat LIKE mkpf-budat,
charg LIKE mseg-charg,
is_return LIKE mseg-smbln,
END OF table_temp.
FIELD-SYMBOLS: <table_temp> LIKE table_temp.
SELECT m1~mblnr m1~mjahr m1~zeile mk~budat m1~charg
INTO CORRESPONDING FIELDS OF TABLE table_temp
FROM mseg AS m1
INNER JOIN mkpf AS mk ON mk~mblnr = m1~mblnr
AND mk~mjahr = m1~mjahr
WHERE m1~ebeln = t_data-ebeln
AND m1~ebelp = t_data-ebelp
AND m1~bwart IN ('101', '103', '105')
.
LOOP AT table_temp ASSIGNING <table_temp>.
SELECT SINGLE mblnr
INTO <table_temp>-is_return
FROM mseg
WHERE smbln = <table_temp>-mblnr
AND sjahr = <table_temp>-mjahr
AND smblp = <table_temp>-zeile
AND bwart = '102'.
ENDLOOP.
Age: 48 Joined: 25 Jan 2008 Posts: 580 Location: Москва
Posted: Wed Nov 05, 2008 8:51 pm Post subject: Re: Тормозит селект по MSEG
corsair wrote:
Здравствуйте. Очень долго работет селект. Можно его оптимизировать?
Code:
DATA: BEGIN OF table_temp OCCURS 0,
mblnr LIKE mseg-mblnr,
budat LIKE mkpf-budat,
charg LIKE mseg-charg,
is_return LIKE mseg-smbln,
END OF table_temp.
SELECT mk~budat m1~mblnr
m1~charg m2~mblnr AS is_return
INTO CORRESPONDING FIELDS OF TABLE table_temp
FROM mseg AS m1
INNER JOIN mkpf AS mk ON mk~mblnr = m1~mblnr AND
mk~mjahr = m1~mjahr
LEFT JOIN mseg AS m2 ON
( m2~smbln = m1~mblnr AND
m2~sjahr = m1~mjahr AND
m2~smblp = m1~zeile AND
m2~bwart = '102' )
WHERE ( m1~ebeln = t_data-ebeln AND
m1~ebelp = t_data-ebelp AND
m1~bwart IN ('101', '103', '105') ).
Если выборка идет по заказам, вместо MSEG лучше использовать таблицу EKBE - там есть как данные mkpf, так и данные mseg (не все конечно, но для выборки данных достаточно).
А про сторно написал vga.
Единственно, что loop .. select ... можно заменить на select ...for all entries, будет небольшое повышение производительности за счет уменьшения количества обращений к БД. _________________ С уважением,
Удав.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
