Select Query:Performance Issue

[Former Member]

Hello Experts,

PLz suggest..

My select query is taking large executio thime even for one month data.

t_header table has around 30,000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into corresponding fields of table gt_s031

for all entries in gt_header

where ( spmon LE v_month_year and

spmon GE month_lastyr ) and

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort.

endif.

v_month_year : sy-date (month & year)

month_lastyr : (month & year) enter by user

Plz suggest how to improve performance of query.

Aastha

[Former Member]

hi,

Try this...

Declare one internal table s031 with ur required fields......

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031 into table gt_s031 for all entries in gt_header

where werks = gt_header-bwkey

and matnr = gt_header-matnr

and lgort = gt_header-lgort

and spmon LE v_month_year

and spmon GE month_lastyr .

endif.

Edited by: Upender Verma on Jan 5, 2009 7:41 AM

[Former Member]

Hello Upender,

This query is taking lot of time for execution, i m not able to get the reason.

Plz tell something to improve my query

types: begin of ty_s031,

spmon type s031-spmon,

werks type s031-werks,

matnr type s031-matnr,

lgort type s031-lgort,

mzubb type s031-mzubb,

wzubb type s031-wzubb,

magbb type s031-magbb,

end of ty_s031.

• Declaration for the Internal-Tables and Work-Areas.

data: gt_s031 type standard table of ty_s031,

gw_s031 type ty_s031.

gt_header has around 30000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort and

spmon LE v_month_year and

spmon GE month_lastyr .

endif.

v_month_year : today's month & year

month_lastyr : user enter month & year as per his requirement

Aastha

[SuhaSaha]

Hello Aastha,

If you check table S031 it has an index on MATNR & you have used MATNR in your selection.

You can remove into corresponding fields addition in the select stmt & check the execution time.

A few points to see:

1. gt_header[] table has how many records?

2. In the where clause :where ( spmon LE v_month_year and spmon GE month_lastyr ), what are the values in v_month_year & month_lastyr . Are you extracting data for a whole year?

BR,

Suhas

[Former Member]

Hello Suhas,

This query is taking lot of time for execution, i m not able to get the reason.

Plz tell something to improve my query

types: begin of ty_s031,

spmon type s031-spmon,

werks type s031-werks,

matnr type s031-matnr,

lgort type s031-lgort,

mzubb type s031-mzubb,

wzubb type s031-wzubb,

magbb type s031-magbb,

end of ty_s031.

• Declaration for the Internal-Tables and Work-Areas.

data: gt_s031 type standard table of ty_s031,

gw_s031 type ty_s031.

gt_header has around 30000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort and

spmon LE v_month_year and

spmon GE month_lastyr .

endif.

v_month_year : today's month & year

month_lastyr : user enter month & year as per his requirement

Aastha

[Former Member]

Hello Suhas,

This query is taking lot of time for execution, i m not able to get the reason.

Plz tell something to improve my query

types: begin of ty_s031,

spmon type s031-spmon,

werks type s031-werks,

matnr type s031-matnr,

lgort type s031-lgort,

mzubb type s031-mzubb,

wzubb type s031-wzubb,

magbb type s031-magbb,

end of ty_s031.

• Declaration for the Internal-Tables and Work-Areas.

data: gt_s031 type standard table of ty_s031,

gw_s031 type ty_s031.

gt_header has around 30000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort and

spmon LE v_month_year and

spmon GE month_lastyr .

endif.

v_month_year : today's month & year

month_lastyr : user enter month & year as per his requirement

Aastha

[Former Member]

Hello Suhas,

This query is taking lot of time for execution, i m not able to get the reason.

Plz tell something to improve my query

types: begin of ty_s031,

spmon type s031-spmon,

werks type s031-werks,

matnr type s031-matnr,

lgort type s031-lgort,

mzubb type s031-mzubb,

wzubb type s031-wzubb,

magbb type s031-magbb,

end of ty_s031.

• Declaration for the Internal-Tables and Work-Areas.

data: gt_s031 type standard table of ty_s031,

gw_s031 type ty_s031.

gt_header has around 30000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort and

spmon LE v_month_year and

spmon GE month_lastyr .

endif.

v_month_year : today's month & year

month_lastyr : user enter month & year as per his requirement

Aastha

[Former Member]

Hello Suhas,

This query is taking lot of time for execution, i m not able to get the reason.

Plz tell something to improve my query

types: begin of ty_s031,

spmon type s031-spmon,

werks type s031-werks,

matnr type s031-matnr,

lgort type s031-lgort,

mzubb type s031-mzubb,

wzubb type s031-wzubb,

magbb type s031-magbb,

end of ty_s031.

• Declaration for the Internal-Tables and Work-Areas.

data: gt_s031 type standard table of ty_s031,

gw_s031 type ty_s031.

gt_header has around 30000 records

if gt_header[] is not initial .

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort and

spmon LE v_month_year and

spmon GE month_lastyr .

endif.

v_month_year : today's month & year

month_lastyr : user enter month & year as per his requirement

Aastha

[Former Member]

Hi,

Avoid using "into corresponding fields of table gt_s031",instead delare the internal table gt_s031 with the required fields only

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header...........

Regards,

Neha

[Former Member]

hi,

avoid the corresponding fields in the select query,

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where

( werks = gt_header-bwkey and

matnr = gt_header-matnr and

lgort = gt_header-lgort ) and

( spmon LE v_month_year and

spmon GE month_lastyr ).

endif.

thank you..

[Former Member]

Hi Aastha ,

Try this out....

select spmon werks matnr lgort mzubb wzubb magbb

from s031

into table gt_s031

for all entries in gt_header

where spmon LE v_month_year

and spmon GE month_lastyr

and werks = gt_header-bwkey

and matnr = gt_header-matnr

and lgort = gt_header-lgort.

endif.

I think this should work as far as the data goes try to give as many primary fields as possible .

Thanks

Sahil

[dev_parbutteea]

Aasta,

you have 30, 000 records in gt_header as you said, so there might be duplicates comparing fields bwkey matnr and lgort. so, you should:

- please make a copy of the gt_header table and do a sort and delete adjacent duplicates (comparing bwkey matnr and lgort) on the copy of gt_header before dong the for all entries.

- also try to put all the fields in the itab gt_s031 in the same order as in the select clause and remove the into corresponding fields clause.

Thanks and regards.

Edited by: Dev Parbutteea on Jan 5, 2009 10:03 AM

[Former Member]

Hi Aastha

Please avoid using INTO CORRESPONDING FIELDS OF itab. This is because after selecting the fields from the database table and before putting them into the itab, it goes and check for the column everytime while putting an entry corresponding to it.

Select the fields in the order of which you have declared them in the internal table and Simply use

Select a b c from dbase

INTO TABLE itab

FOR ALL ENTRIES IN itab1

WHERE xxxx =itab1-yyyy..........

Hope this helps.

[Former Member]

Please remove INTO CORRESPONDING FIELDS OF. Because it will check each and every field while updating entries into corresponding field. Specify the fields in the order in the database table.

Regards,

Joan

[Former Member]

Hello Joan,

I did the same , as I post in my earlier thread..

then also it is showing performance issue.

Aastha

[Former Member]

Moved to ABAP Performance forum...