SAP R/3 форум ABAP консультантов

[Tomatos]

Добрый день! Имеется два запроса, первый выполняется мгновенно, второй минуты 3. Разница в них только в одном условии

mkpf~budat < budat-low и mkpf~budat in budat

SELECT mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg sum( mseg~menge ) as MENGE from mseg join mkpf on "Выбор данных на нижнюю дату
mseg~MBLNR = MKPF~MBLNR and mseg~MJAHR = MKPF~MJAHR
join makt on mseg~MATNR = makt~MATNR into CORRESPONDING FIELDS OF TABLE lt_mseg1 where
mseg~bukrs = BUKRS and mseg~werks in WERKS and mseg~matnr in MATNR and mkpf~budat < budat-low
and mseg~CHARG <> ''
GROUP BY mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg.



SELECT mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg sum( mseg~menge ) as MENGE from mseg join mkpf on "Выбор данных в диапазоне даты
mseg~MBLNR = mkpf~MBLNR and mseg~MJAHR = mkpf~MJAHR
join makt on mseg~MATNR = makt~MATNR into CORRESPONDING FIELDS OF TABLE lt_mseg2 where
mseg~bukrs = BUKRS and mseg~werks in WERKS and mseg~matnr in MATNR and mkpf~budat in budat
and mseg~CHARG <> ''
GROUP BY mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg.


При условии mkpf~budat < budat-high запрос также выполняется мгновенно. Но при выборе в диапазоне, надолго задумывается... Что то можно с этим сделать?

[Удав]

Используйте в выборке хинт SUBSTITUTE_VALUES.
Поищите ноты по MSEG и MKPF в категории performance

[Tomatos]

[Tomatos]

Насколько понял из темы
http://www.sapnet.ru/viewtopic.php?t=3916&highlight=substitutevalues
возможно сперва выборка происходит по таблице mkpf

БД SQL

[Удав]

см. ноту 772497 по хинты.

[Tomatos]

преобразовал запрос следующим образом:

Теперь выбирает мгновенно

SELECT mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg sum( mseg~menge ) as MENGE from mseg join mkpf on "Выбор данных в диапазоне даты
mseg~MBLNR = mkpf~MBLNR and mseg~MJAHR = mkpf~MJAHR
join makt on mseg~MATNR = makt~MATNR into CORRESPONDING FIELDS OF TABLE lt_mseg2 where
( mseg~bukrs = BUKRS and mseg~werks in WERKS and mseg~matnr in MATNR and mseg~CHARG <> '' ) and mkpf~budat > budat-low and mkpf~budat <= budat-high
GROUP BY mseg~bukrs mseg~werks mseg~matnr makt~maktx mseg~shkzg.

[Tomatos]

Пришлось добавить в программу один цикл, и данный селект
SELECT mseg~bukrs mseg~werks mseg~matnr mseg~shkzg sum( mseg~menge ) as MENGE from mseg join mkpf on "Выбор данных в диапазоне даты
mseg~MBLNR = mkpf~MBLNR and mseg~MJAHR = mkpf~MJAHR
into CORRESPONDING FIELDS OF TABLE lt_mseg2 where
mseg~bukrs = BUKRS and mseg~werks in WERKS and mseg~matnr in MATNR and mkpf~budat > budat-low and mkpf~budat <= budat-high and mseg~CHARG <> ''
GROUP BY mseg~bukrs mseg~werks mseg~matnr mseg~shkzg
%_HINTS MSSQLNT '&SUBSTITUTE VALUES&'.

Опять стал грузиться несколько минут. Вообще не пойму ,что еще можно сделать, почему если используется диапазон то он так себя ведет.. ведь при условиях </ > выборка происходит мгновенно

[Удав]

1.Потрассируйте запрос с помощью ST05 и выложите план запроса.
2.Зачем Вам sum и group by? Это дополнительная нагрузка на БД.

[alezhu]

а данные-то те же самые выбираются в обоих случаях? я имею в виду результат одинаков?

[Tomatos]

[Tomatos]

[Удав]

[Tomatos]

Вот результат при выполнении запроса
=========== INPUT ===============================
SELECT T_00."BUKRS" AS c,T_00."WERKS" AS c,T_00."MATNR" AS c,T_00."SHKZG" AS c,
SUM( T_00."MENGE" ) "MENGE"
FROM "MSEG" T_00 INNER
JOIN "MKPF" T_01 ON T_01."MANDT" = 555 AND T_00."MBLNR" = T_01."MBLNR"
AND
T_00."MJAHR" = T_01."MJAHR"
WHERE T_00."MANDT" = 555 AND T_00."BUKRS" = 12 AND T_00."WERKS" = 1203 AND
T_00."MATNR" =
41008649 AND T_01."BUDAT" > '01.01.2012' AND T_01."BUDAT" <=
'14.08.2012' AND T_00."CHARG" <>
'' AND T_00."BWART" <> '342'
GROUP BY T_00."BUKRS",T_00."WERKS",T_00."MATNR",T_00."SHKZG"
/* R3:YMM_REPORT_LIVE_MATER:207
T:MSEG 33441888103*/
/*Dynamic SQL statement -- no cursor, upto 0, conn. 0:1*/
=========== OUTPUT ==============================
SELECT T_00."BUKRS" AS c,T_00."WERKS" AS c,T_00."MATNR" AS c,T_00."SHKZG" AS c,

SUM( T_00."MENGE" ) "MENGE"

FROM "MSEG" T_00 INNER
JOIN "MKPF" T_01 ON T_01."MANDT" = 555 AND T_00."MBLNR" = T_01."MBLNR" AND

T_00."MJAHR" = T_01."MJAHR"

WHERE T_00."MANDT" = 555 AND T_00."BUKRS" = 12 AND T_00."WERKS" = 1203 AND
T_00."MATNR" = 41008649 AND T_01."BUDAT" > '01.01.2012' AND T_01."BUDAT" <=

'14.08.2012' AND T_00."CHARG" <>

'' AND T_00."BWART" <> '342'

GROUP BY T_00."BUKRS",T_00."WERKS",T_00."MATNR",T_00."SHKZG"
/* R3:YMM_REPORT_LIVE_MATER:207 T:MSEG 33441888103*/

/*Dynamic SQL statement -- no cursor, upto 0, conn. 0:1*/
SELECT
|--Stream Aggregate(GROUP BY:([T_00].[BUKRS], [T_00].[WERKS], [T_00].[MATNR], [T_00].[SHKZG]) DEFINE:([Expr1004]=SUM([RPR].[rpr].[MSEG].[MENGE] as [T_00].[MENGE])))
|--Nested Loops(Inner Join, WHERE:([RPR].[rpr].[MSEG].[MBLNR] as [T_00].[MBLNR]=[RPR].[rpr].[MKPF].[MBLNR] as [T_01].[MBLNR] AND [RPR].[rpr].[MSEG].[MJAHR] as [T_00].[MJAHR]=[RPR].[rpr].[MKPF].[MJAHR] as [T_01].[MJAHR]))
|--Sort(ORDER BY:([T_00].[BUKRS] ASC, [T_00].[WERKS] ASC, [T_00].[MATNR] ASC, [T_00].[SHKZG] ASC))
| |--Filter(WHERE:(CONVERT_IMPLICIT(int,[RPR].[rpr].[MSEG].[BUKRS] as [T_00].[BUKRS],0)=(12) AND ([RPR].[rpr].[MSEG].[CHARG] as [T_00].[CHARG]<'' OR [RPR].[rpr].[MSEG].[CHARG] as [T_00].[CHARG]>'')))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([T_00].[MANDT], [T_00].[MBLNR], [T_00].[MJAHR], [T_00].[ZEILE], [Expr1011]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Index Scan(OBJECT:([RPR].[rpr].[MSEG].[MSEG~M] AS [T_00]), WHERE:(CONVERT_IMPLICIT(int,[RPR].[rpr].[MSEG].[MANDT] as [T_00].[MANDT],0)=(555) AND CONVERT_IMPLICIT(int,[RPR].[rpr].[MSEG].[WERKS] as [T_00].[WERKS],0)=(1203) AND CONVERT_IMPLICIT(int,[RPR].[rpr].[MSEG].[MATNR] as [T_00].[MATNR],0)=(41008649) AND ([RPR].[rpr].[MSEG].[BWART] as [T_00].[BWART]<'342' OR [RPR].[rpr].[MSEG].[BWART] as [T_00].[BWART]>'342')))
| |--Clustered Index Seek(OBJECT:([RPR].[rpr].[MSEG].[MSEG~0] AS [T_00]), SEEK:([T_00].[MANDT]=[RPR].[rpr].[MSEG].[MANDT] as [T_00].[MANDT] AND [T_00].[MBLNR]=[RPR].[rpr].[MSEG].[MBLNR] as [T_00].[MBLNR] AND [T_00].[MJAHR]=[RPR].[rpr].[MSEG].[MJAHR] as [T_00].[MJAHR] AND [T_00].[ZEILE]=[RPR].[rpr].[MSEG].[ZEILE] as [T_00].[ZEILE]) LOOKUP ORDERED FORWARD)
|--Table Spool
|--Index Scan(OBJECT:([RPR].[rpr].[MKPF].[MKPF~BUD] AS [T_01]), WHERE:([RPR].[rpr].[MKPF].[BUDAT] as [T_01].[BUDAT]>'01.01.2012' AND [RPR].[rpr].[MKPF].[BUDAT] as [T_01].[BUDAT]<='14.08.2012' AND CONVERT_IMPLICIT(int,[RPR].[rpr].[MKPF].[MANDT] as [T_01].[MANDT],0)=(555)))

=========== INDEXES =============================
MKPF
|-- MKPF~0 ( MANDT, MBLNR, MJAHR )
|-- MKPF~BUD ( MANDT, BUDAT, MBLNR )
MSEG
|-- MSEG~0 ( MANDT, MBLNR, MJAHR, ZEILE )
|-- MSEG~M ( MANDT, MATNR, WERKS, LGORT, BWART, SOBKZ )
|-- MSEG~R ( MANDT, RSNUM )
|-- MSEG~S ( SMBLN, SJAHR, SMBLP )
|-- MSEG~Z03 ( MANDT, CHARG )
|-- MSEG~Z1 ( MANDT, MATNR, CHARG, BWART, BUKRS )
|-- MSEG~Z2 ( MANDT, BWART, MAT_KDAUF, MAT_KDPOS )
=========== COLUMN DENSITIES ====================
MKPF
|-- AWSYS 0.33333334
|-- BKTXT 4.0650405E-3
|-- BLDAT 2.3809524E-2
|-- BUDAT 2.6041667E-3
|-- CPUDT 1.0834236E-3
|-- CPUTM 2.2311468E-4
|-- LE_VBELN 3.8765702E-5
|-- MANDT 0.5
|-- MANDT 0.5
|-- MBLNR 3.4353754E-6
|-- MJAHR 0.2
|-- SPE_LOGSYS 1
|-- USNAM 5.5555556E-2
|-- VGART 0.2
|-- XBLNR 2.7801718E-5
MSEG
|-- BUKRS 0.11111111
|-- BWART 3.9999999E-2
|-- BWLVS 4.7619049E-2
|-- BWTAR 3.0125926E-5
|-- CHARG 7.9021402E-6
|-- EBELN 5.3734554E-4
|-- EBELP 7.6335878E-3
|-- GJAHR 0.2
|-- GRUND 0.25
|-- INSMK 0.33333334
|-- KUNNR 2.2026431E-3
|-- LGORT 0.25
|-- LGTYP 0.1
|-- LIFNR 6.0606059E-3
|-- LINE_ID 5.5248621E-3
|-- MANDT 0.5
|-- MANDT 0.5
|-- MANDT 0.5
|-- MANDT 0.5
|-- MANDT 0.5
|-- MANDT 0.5
|-- MATNR 8.9445437E-4
|-- MAT_KDAUF 4.3478262E-2
|-- MAT_KDPOS 0.125
|-- MBLNR 1.3025921E-4
|-- MENGE 9.8911964E-4
|-- MENGU 1
|-- MJAHR 0.16666667
|-- PARENT_ID 2.2727273E-2
|-- SHKZG 0.5
|-- SJAHR 0.25
|-- SMBLN 5.8479533E-3
|-- SMBLP 2.0408163E-2
|-- SOBKZ 0.33333334
|-- UMMAT 3.0788177E-4
|-- WEMPF 1.4164306E-3
|-- WERKS 0.03846154
|-- WERTU 1
|-- XAUTO 0.5
|-- ZEILE 4.5662099E-3

[Tomatos]

Вот план запроса, который выполняется мгновенно

SELECT T_00."BUKRS" AS c,T_00."WERKS" AS c,T_00."MATNR" AS c,T_02."MAKTX" AS c,
T_00."SHKZG" AS c,SUM( T_00."MENGE" ) "MENGE"
FROM "MSEG" T_00 INNER
JOIN "MKPF" T_01 ON T_01."MANDT" = @P0 AND T_00."MBLNR" = T_01."MBLNR" AND
T_00."MJAHR" = T_01."MJAHR" INNER
JOIN "MAKT" T_02 ON T_02."MANDT" = @P1 AND T_00."MATNR" = T_02."MATNR"
WHERE T_00."MANDT" = @P2 AND T_00."BUKRS" = @P3 AND T_00."WERKS" = @P4
AND T_00."MATNR" = @P5 AND T_01."BUDAT" < @P6 AND T_00."CHARG" <> @P7 AND
T_00."BWART" <> @P8
GROUP BY T_00."BUKRS",T_00."WERKS",T_00."MATNR",T_02."MAKTX",T_00."SHKZG"
/* R3:YMM_REPORT_LIVE_MATER:134 T:MSEG 33344188103*/
/*Dynamic SQL statement -- no cursor, upto 0, conn. 0:1*/
SELECT
|--Stream Aggregate(GROUP BY:([T_00].[SHKZG], [T_02].[MAKTX]) DEFINE:([Expr1006]=SUM([RPR].[rpr].[MSEG].[MENGE] as [T_00].[MENGE]), [T_00].[BUKRS]=ANY([RPR].[rpr].[MSEG].[BUKRS] as [T_00].[BUKRS]), [T_00].[WERKS]=ANY([RPR].[rpr].[MSEG].[WERKS] as [T_00].[WERKS]), [T_00].[MATNR]=ANY([RPR].[rpr].[MSEG].[MATNR] as [T_00].[MATNR])))
|--Sort(ORDER BY:([T_00].[SHKZG] ASC, [T_02].[MAKTX] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([T_00].[MBLNR], [T_00].[MJAHR], [Expr1009]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Nested Loops(Inner Join)
| |--Clustered Index Seek(OBJECT:([RPR].[rpr].[MAKT].[MAKT~0] AS [T_02]), SEEK:([T_02].[MANDT]=[@P1] AND [T_02].[MATNR]=[@P5]) ORDERED FORWARD)
| |--Filter(WHERE:([RPR].[rpr].[MSEG].[BUKRS] as [T_00].[BUKRS]=[@P3] AND ([RPR].[rpr].[MSEG].[CHARG] as [T_00].[CHARG]<[@P7] OR [RPR].[rpr].[MSEG].[CHARG] as [T_00].[CHARG]>[@P7])))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([T_00].[MANDT], [T_00].[MBLNR], [T_00].[MJAHR], [T_00].[ZEILE], [Expr1008]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Index Seek(OBJECT:([RPR].[rpr].[MSEG].[MSEG~M] AS [T_00]), SEEK:([T_00].[MANDT]=[@P2] AND [T_00].[MATNR]=[@P5] AND [T_00].[WERKS]=[@P4]), WHERE:([RPR].[rpr].[MSEG].[BWART] as [T_00].[BWART]<[@P8] OR [RPR].[rpr].[MSEG].[BWART] as [T_00].[BWART]>[@P8]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([RPR].[rpr].[MSEG].[MSEG~0] AS [T_00]), SEEK:([T_00].[MANDT]=[RPR].[rpr].[MSEG].[MANDT] as [T_00].[MANDT] AND [T_00].[MBLNR]=[RPR].[rpr].[MSEG].[MBLNR] as [T_00].[MBLNR] AND [T_00].[MJAHR]=[RPR].[rpr].[MSEG].[MJAHR] as [T_00].[MJAHR] AND [T_00].[ZEILE]=[RPR].[rpr].[MSEG].[ZEILE] as [T_00].[ZEILE]) LOOKUP ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([RPR].[rpr].[MKPF].[MKPF~0] AS [T_01]), SEEK:([T_01].[MANDT]=[@P0] AND [T_01].[MBLNR]=[RPR].[rpr].[MSEG].[MBLNR] as [T_00].[MBLNR] AND [T_01].[MJAHR]=[RPR].[rpr].[MSEG].[MJAHR] as [T_00].[MJAHR]), WHERE:([RPR].[rpr].[MKPF].[BUDAT] as [T_01].[BUDAT]<[@P6]) ORDERED FORWARD)


Получается, предыдущий запрос строится по всей таблице MKPF без связи с MSEG, поэтому выполняется долго.

Что можно сделать?

[Удав]