Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select Query is taking lot of time.

Former Member
0 Kudos

Hi Experts,

Here is the query i have used.

Its taking a lot of tome and sometimes its timing out.

SELECT mvgr1 "Line of Business

werks "Plant

lgort "Storage Location

charg "Batch

matnr "Material Number

kwmeng "Ordered quantity

posnr "Item

vbeln "Sales Order Number

FROM vbap

INTO TABLE gt_salesdatatemp

FOR ALL ENTRIES IN gt_matmerge

WHERE matnr EQ gt_matmerge-matnr

AND werks EQ gt_matmerge-werks

AND mvgr1 EQ gt_matmerge-mvgr1

AND lgort EQ gt_matmerge-lgort

AND charg EQ gt_matmerge-charg

AND abgru EQ space.

i didnt use primary keys of VBAP table, Could you please suggest me how to improve the performance of this query.

Thanks & Regards,

Krishna Reddy.T

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

Check if the internal table gt_salesdatatemp is not initial before the Select.

how many entries are expected from this select? If required, assuming it takes lot of time to fetch data from VBAP, then create an index with MATNR, WERKS & LGORT.

Do this only if it required and the volume of data is large.

regards,

madhu

8 REPLIES 8

former_member188685
Active Contributor
0 Kudos

before using for all entries check the initiial

may be you have no entries in that table and it is reading the whole table.

if gt_matmerge is not initial.

SELECT mvgr1 "Line of Business

werks "Plant

lgort "Storage Location

charg "Batch

matnr "Material Number

kwmeng "Ordered quantity

posnr "Item

vbeln "Sales Order Number

FROM vbap

INTO TABLE gt_salesdatatemp

FOR ALL ENTRIES IN gt_matmerge

WHERE matnr EQ gt_matmerge-matnr

AND werks EQ gt_matmerge-werks

AND mvgr1 EQ gt_matmerge-mvgr1

AND lgort EQ gt_matmerge-lgort

AND charg EQ gt_matmerge-charg

AND abgru EQ space.

endif.

Former Member
0 Kudos

Hi,

Check if the internal table gt_salesdatatemp is not initial before the Select.

how many entries are expected from this select? If required, assuming it takes lot of time to fetch data from VBAP, then create an index with MATNR, WERKS & LGORT.

Do this only if it required and the volume of data is large.

regards,

madhu

kesavadas_thekkillath
Active Contributor
0 Kudos

it just because u have not used vbeln and posnr..

Try to include it some how.....

try some thing ike this...it will show some difference i guess...

data:v_vbeln type vbap-vbeln value is initial.

data:v_posnr type vbap-posnr value is initial.

SELECT mvgr1 "Line of Business

werks "Plant

lgort "Storage Location

charg "Batch

matnr "Material Number

kwmeng "Ordered quantity

posnr "Item

vbeln "Sales Order Number

FROM vbap

INTO TABLE gt_salesdatatemp

FOR ALL ENTRIES IN gt_matmerge

WHERE

vbeln ge v_vbeln

and posnr ge v_posnr

and matnr EQ gt_matmerge-matnr

AND werks EQ gt_matmerge-werks

AND mvgr1 EQ gt_matmerge-mvgr1

AND lgort EQ gt_matmerge-lgort

AND charg EQ gt_matmerge-charg

AND abgru EQ space.

Former Member
0 Kudos

Hi Experts,

Here is the query i have used.

Its taking a lot of tome and sometimes its timing out.

There are more than 4lakh records in GT_MATMERGE.

SELECT mvgr1 "Line of Business

werks "Plant

lgort "Storage Location

charg "Batch

matnr "Material Number

kwmeng "Ordered quantity

posnr "Item

vbeln "Sales Order Number

FROM vbap

INTO TABLE gt_salesdatatemp

FOR ALL ENTRIES IN gt_matmerge

WHERE matnr EQ gt_matmerge-matnr

AND werks EQ gt_matmerge-werks

AND mvgr1 EQ gt_matmerge-mvgr1

AND lgort EQ gt_matmerge-lgort

AND charg EQ gt_matmerge-charg

AND abgru EQ space.

i didnt use primary keys of VBAP table, Could you please suggest me how to improve the performance of this query.

Thanks & Regards,

Krishna Reddy.T

0 Kudos

Funny, I have seen this same code before, here:

you guys have some sort of code exchange going?

kesavadas_thekkillath
Active Contributor
0 Kudos

TRy using cursors...Make a search in forums for cursor concept...

Former Member
0 Kudos

one more effective way is to create INDEX on the one of the field which u r using in where condition. It will be more effective than any other.

You can create INDEX on matnr

Regards,

Swarup

Former Member
0 Kudos

Hi RamaKrishna,

I think your gt_matmerge table is empty. If the for all entries table is empty then fetches all the records ignoring the conditions in WHERE clause.

Make sure that the table gt_matmerge is not empty.

And the second reason is that you did not use the primary key fields of the table. Because the non-key field values are not unique and they may contain many records satisfying that conditons.

Hope this helps you.

Regards,

Chandra Sekhar