SQL 2005 + ERP 7.01 select from (select union) where - massive perform.prob

[Former Member]

Dear @all, we found in our ERP system a lot selects with the union command. The SQL server select in such kinds all data of the table and do with a filter a nested loop over this data. The ivolved tables are f.e. VBAP, MSEG, VBKD and VBEP and the select search for unique entries in VBLEN. If I do a select single it is searching correctly in the primary clustered index. But with union it woks corios... I would assume that it would be better the MSSQL server do 50 single selects than a clustered index scan with all entries.

The select to one VBELN needs about 700ms, starting from 2 values and more with union it needs much more time and transfers for VBKD for every select about 700 MB over the network.

SELECT

T_00.MANDT,T_00.VBELN,T_00.POSNR,T_00.KONDA,T_00.KDGRP,T_00.BZIRK,

T_00.PLTYP,T_00.INCO1,T_00.INCO2,T_00.KZAZU,T_00.PERFK,T_00.PERRL,T_00.MRNKZ,

T_00.KURRF,T_00.VALTG,T_00.VALDT,T_00.ZTERM,T_00.ZLSCH,T_00.KTGRD,T_00.KURSK,

T_00.PRSDT,T_00.FKDAT,T_00.FBUDA,T_00.GJAHR,T_00.POPER,T_00.STCUR,T_00.MSCHL,

T_00.MANSP,T_00.FPLNR,T_00.WAKTION,T_00.ABSSC,T_00.LCNUM,T_00.J_1AFITP,

T_00.J_1ARFZ,T_00.J_1AREGIO,T_00.J_1AGICD,T_00.J_1ADTYP,T_00.J_1ATXREL,

T_00.ABTNR,T_00.EMPST,T_00.BSTKD,T_00.BSTDK,T_00.BSARK,T_00.IHREZ,T_00.BSTKD_E,

T_00.BSTDK_E,T_00.BSARK_E,T_00.IHREZ_E,T_00.POSEX_E,T_00.KURSK_DAT,

T_00.KURRF_DAT,T_00.KDKG1,T_00.KDKG2,T_00.KDKG3,T_00.KDKG4,T_00.KDKG5,

T_00.WKWAE,T_00.WKKUR,T_00.AKWAE,T_00.AKKUR,T_00.AKPRZ,T_00.J_1AINDXP,

T_00.J_1AIDATEP,T_00.BSTKD_M,T_00.DELCO,T_00.FFPRF,T_00.BEMOT,T_00.FAKTF,

T_00.RRREL,T_00.ACDATV,T_00.VSART,T_00.TRATY,T_00.TRMTYP,T_00.SDABW,T_00.WMINR,

T_00.FKBER,T_00.PODKZ,T_00.CAMPAIGN,T_00.VKONT,T_00.DPBP_REF_FPLNR,

T_00.DPBP_REF_FPLTR,T_00.REVSP,T_00.REVEVTYP,T_00.VTREF,T_00.PEROP_BEG,

T_00.PEROP_END,T_00.STCODE,T_00.FORMC1,T_00.FORMC2,T_00.STEUC,T_00.OIC_TIME,

T_00.OITITLE,T_00.OIPTRM1,T_00.OIPTRM2,T_00.OIPTRM3,T_00.OIPTRM4,T_00.OIPTRM5,

T_00.OIPTRM6,T_00.OIPTRM7,T_00.OIPTRM8,T_00.OIPTRM9,T_00.OIPFLIC,T_00.COMPREAS

FROM qto.VBKD T_00,(

SELECT C_01 = 0000000004 UNION

SELECT 0000000005 UNION

SELECT 0000000006 ) T_01

WHERE T_00.MANDT = 100 AND T_00.VBELN = T_01.C_01

Link to the execution plans: fist with union, second withoud:

http://img196.imageshack.us/i/unbenannttfq.png

[Former Member]

under high load the database processes of the DB user <sapsid> are waiting for simple and very fast running sql statements in the process queue with the argument PAGEIOLATCH_SH. If I call such kind of statements in parallel with the same where clause in the enterprise manager with a local system admin account, these requests will be finished immediately by high load (by the seemingly locking situation). Why the sap processes are seemingly queued and waiting for one of the others. An on the other hand I could not see the main process for the "lock".

[xymanuel]

Hi René,

is it possible that an "for all entries" Statement is causing this Problem?

If yes, check Note 77013.

Kind regards

Manuel

[Former Member]

thx a lot, but all these paramereters are set to default values and are identically with the values of this note.