slow query

[Former Member]

Hi,

I have a problem in a query given below , can somebody help me how to optimize the below query so that that could be selected fast as it is giving time-out abap dump

SELECT

a~vbeln

b~posnr

b~matnr

a~vkorg

a~vtweg

a~spart

a~erdat

a~ernam

a~kunnr

a~ctlpc

a~auart

a~kkber

a~sbgrp

b~zzdpdat

b~yywi17

b~kwmeng

b~kzwi1

b~yywi21

b~abgru

b~zzmfrpn

b~zzeddat

FROM vbak AS a INNER JOIN vbap AS b ON a_{vbeln = b}vbeln

into TABLE i_vbak_vbap

WHERE a~vbeln IN s_vbeln

AND a~vkorg IN s_vkorg

AND a~vtweg IN s_vtweg

AND a~spart IN s_spart

AND a~auart IN s_auart

AND a~erdat IN s_erdat

AND a~ernam IN s_ernam

AND a~kunnr IN s_kunnr.

thanks in advance

[Former Member]

Hello Bobby,

The code which u have written is correct.

If u want improve the performance then create the secondary index for the tables.

If useful reward points.

Vasanth

[Former Member]

Hi,

Is there any other way by which I can improve the performance of query programatically

thanks

bobby

[LucianoBentiveg]

Are all descrpted in this thread i think. Now depends of user selection criteria.

Regards.

[Former Member]

Hi,

You are using lot of parameters in WHERE clause but which parameters you are using mainly in seletion criteria. If you as using sales document its well and good. If other than primary key fields then go ahead to create secondary indexs as per many friends suggested already.

thanks

Bt

[Former Member]

Hi,

Try to use secondary index if you are not passing

vbeln to select query.

what are the obligatory fields in your select?

Regards,

Amole

[Former Member]

Hi,

The obligatory fields are sales organisation (vkorg), distribution channel (vtweg) and division (spart),

can you suggest on which fields secondary index will be suitable for the query

thanks

pankaj

[Former Member]

Hi ,

You can avoid time out adap dump by writing the select in a Funcion moulde and call this function module in background.

CALL FUNCTION <func> IN BACKGROUND TASK

This is not an ideal solution, but it may help you to avoid dump.

[Former Member]

Hey,

Instead of selecting the data from VBAK and VBAP, if feasible you might consider selecting data from VAKPA or VAPMA.

These are index tables provided by SAP for reporting purpose.

-Kiran

[former_member226234]

Hi,

The query look fine. I would suggest, if possible try to reduce the number of parameters in the where clause. This is because internally a single query if fired for each where clause.

Regards,

Sandeep

[Former Member]

Hi,

Use secondary index VBAK_B and use for allentries

option instead of inner join.

Regards,

Amole

[Former Member]

Hi,

can you send me the index details of index VBAK_B, because I could not find it in my system

regards

bobby

[varun_maharshi]

Your main selection criteria is on the VBAK table for which you have primary key VBELN. If you want to reduce the load on Database server get all the values with VBELN and then weed out other values according to the selection criteria then use for all entries with the filter table on VBAP. I think this will optimize your query