topic Re: Select Query:Performance Issue in Application Development and Automation Discussions

Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 06:35:11 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 06:41:22 GMT

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

Re: Select Query:Performance Issue

SuhaSaha — Mon, 05 Jan 2009 06:41:40 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 06:43:04 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 06:45:45 GMT

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..

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 06:49:17 GMT

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

Re: Select Query:Performance Issue

dev_parbutteea — Mon, 05 Jan 2009 08:59:29 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 09:18:47 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 09:18:58 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 09:20:07 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 11:34:37 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 11:35:08 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 11:47:03 GMT

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.

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 11:56:17 GMT

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

Re: Select Query:Performance Issue

Former Member — Mon, 05 Jan 2009 12:00:09 GMT

Moved to ABAP Performance forum...

Re: Select Query:Performance Issue

Former Member — Tue, 06 Jan 2009 06:15:33 GMT

Hello Joan,

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

then also it is showing performance issue.

Aastha