topic Select query taking more time.. in Application Development and Automation Discussions

Select query taking more time..

former_member192432 — Thu, 07 Jan 2010 10:07:11 GMT

Hi friends..

The below inner join statement is taking more time , can any body sugget me to improve the performance . I tried FOR ALL ENTRIES also but that also taking more time than inner join statement .

SELECT a~vbeln from vbap as a inner join vakpa as b

on a_{vbeln = b}vbeln

into corresponding fields of table IT_VAKPA

where a~WERKS IN S_IWERKS

and a~pstyv NE 'ZRS'

and b~vkorg = IVKORG

and b~audat IN IAUDAT

and b~vtweg IN IVTWEG.

Regards

Chetan

Re: Select query taking more time..

Rui_Dantas — Thu, 07 Jan 2010 10:24:09 GMT

Hi.

VAKPA is an index table to quickly get sales orders for a given partner (sold-to party, bill-to, ship-to, etc.). It only makes sense to use it if yo know the partner number; in your case you don't, so it doesn't make sense to use it.

If what you need is to filter by VKORG, VTWEG, AUDAT then replace VAKPA by VBAK.

Rui Dantas

Re: Select query taking more time..

Former Member — Thu, 07 Jan 2010 11:17:41 GMT

Hi Chetan ,

VAKPA is an index table. From the select query , it has been observed that you are not fetching any data from VAKPA. Only you have added some selection paramenters in where clause of select query.

My suggestion will be instead of using VAKPA in inner join you use VBAK along with VBAP. All the fields that you are using as selection condition from VAKPA are there in VBAK.

I am sure performance of query will be improved.

If still duo to business logic you need to use VAKPA, try to create secondary non unique index on fields VKORD,AUDATand VTWEG on table VAKPA.

However I will recommend you to go for first option only. If this does not work then go for second option.

Hopfully this will help you.

Regards,

Nikhil

Re: Select query taking more time..

Former Member — Thu, 07 Jan 2010 16:21:32 GMT

Hi Chetan ,

Prefer not to use NE in select query and also into corresponding fields of table.

Try this



SELECT a~vbeln from vbap as a inner join vakpa as b
on a~vbeln = b~vbeln
into corresponding fields of table IT_VAKPA "Try to avoid this
where a~WERKS IN S_IWERKS
"and a~pstyv NE 'ZRS'
and b~vkorg = IVKORG
and b~audat IN IAUDAT
and b~vtweg IN IVTWEG.

Delete from table T_VAKPA where pstyv = 'ZRS'.

Hope this helps you.

Edited by: Harsh Bhalla on Jan 7, 2010 9:52 PM

Re: Select query taking more time..

Former Member — Thu, 07 Jan 2010 16:37:05 GMT

NE in a where is OK so long as the other key fileds in the WHERE are selective enough.

There is nothing wrong with using INTO CORRESPONDING.

Rob

Re: Select query taking more time..

former_member192432 — Fri, 08 Jan 2010 09:32:15 GMT

Thanks for reply....