SQL Query Performance Issue

[Former Member]

Dear Experts,

I have a below SQL Query which gets executed for more than 15 to 20mins, I tried to replace VBPA with VAKPA table, but I don't fine entries related to partners 'ZM' and 'ZQ' within it. Can you please help me how to optimize the below Select Query or any other way to get the similar data. (Date range RT_DATES is 3 months).

SELECT a~vbeln a~erdat a~audat a~auart a~lifsk
a~faksk a~netwr a~waerk a~vkorg a~vtweg a~objnr
FROM vbak AS a INNER JOIN vbpa AS b
ON a~vbeln = b~vbeln
INTO TABLE lit_vbak
WHERE a~erdat IN rt_dates AND
a~auart IN pt_ordtype AND
b~posnr = '000000' AND
b~parvw IN ('ZM', 'ZQ'). "Employee responsible & Requestor

Regards,

[BiberM]

I don't know those tables by heart but one thing you can always do: Push down filtering BEFORE joining. You can achieve this by moving the filtering of table b from the where-clause to the on-clause.

[michael_piesche]

Big Performance Problem regarding VBAK:

Most likely you dont have an index on field VBAK~ERDAT or it is not active (ERDAT - Date on Which Record Was Created).

Check the Indexes for VBAK and look for standard index ERD. In our system it is also not created on database. Instead we use AUD for field AUDAT - Document Date (Date Received/Sent).

If ERDAT is mandatory based on your requirements and AUDAT can not be used, check with your organisation whether index ERD can be activated on DB.

(I would say in 99% those two dates should be the same, and if not, maybe differ in one day if e.g. Idoc was sent before midnight but order was created after midnight, in case of replication issues it can be a bigger difference though, but that should be an exception).

I would assume, that for a 3 month range, you will not have to build another index that includes ERDAT/AUDAT as well as AUART, unless you do have 'millions' of records during that time frame. So an index on ERDAT/AUDAT should work well regarding VBAK. Side note: MANDT does not necessarely have to be part of the index for AUDAT or ERDAT (unless you have multiple clients with lots of data), but it isnt that 'bad' either and used to be standard SAP procedure for creating indexes.

Little Performance Problem regarding VBPA:

You already have the first part of VBPA key in your join statement, but you could also add POSNR = '000000' as well. This would add also a little performance gain as well. You are not able to add PARVW into the join statement, because it is not a straight forward 'equals' restriction, so it stays in the where condition.

SELECT a~vbeln a~erdat a~audat a~auart a~lifska~faksk a~netwr a~waerk a~vkorg a~vtweg a~objnr
FROM vbak AS a 
INNER JOIN vbpa AS b
   ON b~vbeln = a~vbeln
  AND b~posnr = '000000'
INTO TABLE lit_vbak
WHERE a~erdat IN rt_dates      " make sure this field is indexed
  AND a~auart IN pt_ordtype
  AND b~parvw IN ('ZM', 'ZQ'). " Employee responsible & Requestor

[Former Member]

Thanks for your suggestion, Unfortunately I cannot use AUDAT as I can see there are couple of days in difference between ERDAT and AUDAT for many Sales orders in our Production Server. And ERD is not activated in my system and I doubt whether my client will allow to activate it. Is there any other way to Split my Select Query or any Parallel Processing can be done for my Query? Please help me here to resolve this issue.

Regards,

[michael_piesche]

saleem.sharukh, why would your client not allow that? Is that report so unimportant to him?

What you could also do, is to have AUDAT and ERDAT in your select statement and make the range of AUDAT by lets say a month smaller and bigger than your ERDAT. That is, if your ERDAT range only contains ‚simple‘ range values that are ‚easy‘ to transform in appropriate AUDAT ranges.

ERDAT between 01.02.2019 and 01.05.2019 (rt_dates)

AUDAT between 01.01.2019 and 01.06.2019 (rt_dates2)

You could also fine tune that depending on your knowledge of the data and the possible range values.

SELECT a~vbeln a~erdat a~audat a~auart a~lifska~faksk a~netwr a~waerk a~vkorg a~vtweg a~objnr
FROM vbak AS a 
INNERJOIN vbpa AS b
   ON b~vbeln = a~vbeln
  AND b~posnr ='000000'INTOTABLE lit_vbak
WHERE a~audat IN rt_dates2  " make sure this field is indexed
  AND a~erdat IN rt_dates      
  AND a~auart IN pt_ordtype
  AND b~parvw IN('ZM','ZQ'). " Employee responsible & Requestor

[Former Member]

I have activated the Index ERD as suggested, now my query execution time takes less than 4 minutes. Thanks all for your valuable suggestions.

[Jelena_Perfiljeva]

Regarding VAKPA table - it's not populated for all the partners by default. There is an option in configuration (SPRO) to fill in that table for other partner types than Sold-to. Ask your SD consultant.

But you'll still face an issue with VBAK, I suspect, if there is no suitable active secondary index, as Michael noted.

You can always use ST05 (SQL Trace) to see the exact execution path in the DB. I highly recommend this when troubleshooting any performance issues.

[Former Member]

Hello,

Here is the execution path taken from my DEV system. Please help me here how to optimize the query.

[RaymondGiuseppi]

Check index VBAK~ERD activation in your database.

[Sandra_Rossi]

As a rule-of-thumb, to analyze any SQL performance issue, look at the execution plan (via SQL trace ST05). Attach it here if you need help to analyze it.