Performance of Select query

[Former Member]

Hi All,

I am using select stmt on MARA and ZTSDNETPR.

In ZTSDNETPR table, pltyp,konda,MATNR,DATAB,DATBI are indexes in same order as i am using in my select stmt.

But it is taking 1 hrs 45 min to take execute. Please give me any suggestion.

SELECT MATNR MTART MATKL ZPLINE ZREL ZLANG ZUSG_TYPE

ZDEPLOY ZLIC_TYPE ZSER_TYPE ZSVC_SALE_TYPE

ZSUB_LEVEL ZSUB_REL ZASM ZREF_TYPE ZUPD_FRM

FROM MARA

INTO CORRESPONDING FIELDS OF TABLE IT_MATERIAL_INFO

WHERE MATNR IN S_MATNR

AND MATKL IN S_MATKL

AND ERSDA IN S_ERSDA.

IF SY-SUBRC = 0.

sort it_material_info by matnr.

***Fetching the price details from ZTSDNETPR table

select pltyp konda MATNR datab datbi

from ZTSDNETPR

into TABLE IT_ZTSDNETPR

for all entries in it_material_info

where

pltyp <> space

and konda <> space

AND MATNR = it_material_info-matnr

AND DATAB <= sy-datum

AND DATBI >= sy-datum.

Regards,

Shiv.

[Former Member]

HI,

In the second select statement also u use into corresponding fields of table.

[Former Member]

Hi Shivashankar,

Could you please tell us

How many records are there in MARA ?

How many Records in the Z table?

Tell us which select statement is taking more than 1 hour.

Number of records in Z table where pltyp <> space and konda <> space.

Regards,

Wiboon

[Former Member]

hi,

1. declare the internal table in the same order as it is in MSEG,

2. in select query mention the fields in order as in the ITAB

3. in where condition, mention the key fields first and maintain the order of fields as in MSEG

def it will increase performance..

With Rgds,

S.Barani

[former_member194669]

Hi,

By simply giving a space in the below mentioned fields in the select will not considered for secondary index. You need to provide values for these two fields

pltyp <> space

and konda <> space

a®

[kiran_k8]

shiv,

SELECT MATNR MTART MATKL ZPLINE ZREL ZLANG ZUSG_TYPE

ZDEPLOY ZLIC_TYPE ZSER_TYPE ZSVC_SALE_TYPE

ZSUB_LEVEL ZSUB_REL ZASM ZREF_TYPE ZUPD_FRM

FROM MARA

INTO CORRESPONDING FIELDS OF TABLE IT_MATERIAL_INFO

<b>WHERE MATNR IN S_MATNR

AND MATKL IN S_MATKL

AND ERSDA IN S_ERSDA.</b>

changed to

SELECT MATNR MTART MATKL ZPLINE ZREL ZLANG ZUSG_TYPE

ZDEPLOY ZLIC_TYPE ZSER_TYPE ZSVC_SALE_TYPE

ZSUB_LEVEL ZSUB_REL ZASM ZREF_TYPE ZUPD_FRM

FROM MARA

INTO CORRESPONDING FIELDS OF TABLE IT_MATERIAL_INFO

WHERE MATNR IN S_MATNR.

loop at it_material_info.

if ( it_material_info-matkl not in s_matkl ) or ( it_material_info-ersda not in s_ersda )

delete it_material_info.

endif.

endloop.

In your previous query the fields given in the where clause are not primary keys.Always make it a point to use only primary keys or index in the where clause.This will enhance the performance.

So,I had changed the select query accordingly and then filtered it w.r.t the selection-screen matkl and ersda.

The same you apply for the ztable also.This will surely enhance the performance.

K.Kiran.

Message was edited by:

Kiran K

[Former Member]

Hi Shankar,

Try as follows instead of <> in the condition, use NOT INITIAL in the condition.

select pltyp konda MATNR datab datbi

from ZTSDNETPR

into TABLE IT_ZTSDNETPR

for all entries in it_material_info

where

<b>pltyp IS NOT INITIAL

and konda IS NOT INITIAL</b>

AND MATNR = it_material_info-matnr

AND DATAB <= sy-datum

AND DATBI >= sy-datum.

Thanks,

Vinay

[Former Member]

Hi,

I can understand what is your problem. Try to make your for all entries condition first then write other condition.

select pltyp konda MATNR datab datbi

from ZTSDNETPR

into TABLE IT_ZTSDNETPR

for all entries in it_material_info

on AND MATNR = it_material_info-matnr

where

pltyp <> space

and konda <> space

AND DATAB <= sy-datum

AND DATBI >= sy-datum.

Rewards points if it is useful.

[Former Member]

Thanks for Replies all,

It is taking time in select query on ZTSDNETPR.

I have used ur hints, but still it is taking time.

Regards,

Shiv.

[Former Member]

hi

u will create index at database level.

then automatically it will improve performance.

cheers,

raju.m

[Former Member]

Thanks for ur reply,

Indexs is already created at database level.

But still is slow.

Regards,

Shiv.