Skip to Content
0
Former Member
Jan 17, 2011 at 04:00 PM

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

40 Views

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