topic Re: Performance issue with table VBAK. in Application Development and Automation Discussions

Performance issue with table VBAK.

Former Member — Fri, 18 Feb 2011 10:04:49 GMT

SQL Statement

SELECT

"VBELN", "ERDAT", "ERZET", "AUART", "VKORG", "VTWEG", "VKBUR", "KNUMV",

"VDATU", "BSTNK", "BSARK", "KUNNR"

FROM

"VBAK"

WHERE

"MANDT" = ? AND ( "ERDAT" = ? AND "ERZET" > ? OR "ERDAT" > ? ) AND "AUART"

IN ( ?, ? ) AND "VKORG" IN ( ?, ?, ?, ?, ?, ?, ?, ? ) AND "VTWEG" IN ( ?,

?, ?, ?, ?, ?, ? ) AND "BSARK" = ? WITH UR

Access Plan Opt Level = 5 ; Parallelism = None

0 SELECT STATEMENT ( Estimated Costs = 3,258E+07 [timerons] )

1 RETURN

2 NLJOIN

3 [O] TBSCAN

4 SORT

5 TBSCAN GENROW

6 <i> FETCH VBAK

7 IXSCAN VBAK~ZL1 #key columns: 2

Source Code : /AMS/OUSCSR_ORDER_EXCEP_FORM

SELECT vbeln "Sales document no

*-Begin of Mod-013>>

erdat

erzet

*-End of Mod-013<<

auart "Sales document type

vkorg "sales org

vtweg "dist chnl

vkbur "Sales office

knumv "No of document condition

vdatu "Requested delivery date

bstnk "Purchase order no

bsark "Purchase order type

kunnr "Sold to number

INTO TABLE fp_i_vbak

FROM vbak

WHERE vbeln IN s_vbeln

AND erdat GE v_start_date

AND auart IN s_ordty

AND vkorg IN s_salorg

AND vtweg IN s_discha

AND vkbur IN s_saloff

AND bsark IN s_purty

AND kunnr IN s_soldto .

*-Begin Of Mod-013>>

IF sy-subrc IS INITIAL.

AND erzet > v_start_time )

OR erdat > v_start_date )

DELETE fp_i_vbak WHERE erdat LT v_start_date .

DELETE fp_i_vbak WHERE erdat EQ v_start_date

AND erzet LT v_start_time.

The index used is :

6 <i> FETCH VBAK

7 IXSCAN VBAK~ZL1 #key columns: 2

NONUNIQUE Index VBAK~ZL1

Column Name # Distinct

MANDT 1

VKORG 154

ERDAT 2.312

BSTNK 8.529.428

KUNNR 818.388

Please optimize the SQL. Only 2 key columns used .. MANDT , VKORG. This is not optimal. The code

AND ( ( erdat = v_start_date AND erzet > v_start_time )

OR erdat > v_start_date )

should be reviewed. This is confusing the DB2 optimizer.

Hi All please look this issue any one suggest me where is problem with select querry and how improve performance of vbak select querry.

Re: Performance issue with table VBAK.

brad_bohn — Fri, 18 Feb 2011 14:56:37 GMT

That's a bad SELECT statement; you can't expect that to be optimal when you have that many sales orgs and distinct date values and you aren't specifying any other field which might increase the selectivity or trigger another index to be picked up. You need to reduce your selection to specific customers, PO values, materials, smaller date range or something and consider using one of the order index tables as well.

Re: Performance issue with table VBAK.

Former Member — Fri, 18 Feb 2011 19:34:18 GMT

I agree that this is not good select statement, but think it can be improved with some changes....

suggestions:

1. remove all date fields from select statement

2. make a range for auart and vkorg

3. after select check the date condition and delete entries

Thanks.

Re: Performance issue with table VBAK.

brad_bohn — Fri, 18 Feb 2011 22:49:10 GMT

I don't see how replacing select-options with ranges is a performance boost? Nor is removing date ranges unless there's no other way to stop a bad index from being selected.

Re: Performance issue with table VBAK.

Former Member — Fri, 25 Feb 2011 20:35:53 GMT

Make sure you have an index that includes ERDAT and ERZET, since you're doing a comparison with it.