topic Re: slow query in Application Development and Automation Discussions

slow query

Former Member — Mon, 17 Jul 2006 10:04:08 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:08:04 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:09:23 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:14:42 GMT

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.

Re: slow query

Former Member — Mon, 17 Jul 2006 10:23:37 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:24:28 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:25:04 GMT

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

Re: slow query

Former Member — Mon, 17 Jul 2006 10:34:04 GMT

Hi,

Use secondary index VBAK_B and use for allentries

option instead of inner join.

Regards,

Amole

Re: slow query

Former Member — Mon, 17 Jul 2006 10:51:50 GMT

Hi,

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

regards

bobby

Re: slow query

Former Member — Mon, 17 Jul 2006 13:25:40 GMT

Hi,

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

thanks

bobby

Re: slow query

LucianoBentiveg — Mon, 17 Jul 2006 13:28:53 GMT

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

Regards.

Re: slow query

Former Member — Mon, 17 Jul 2006 13:51:32 GMT

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

Re: slow query

varun_maharshi — Mon, 17 Jul 2006 13:54:34 GMT

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