topic HOW SHOULD MAKE A SINGLE QUERY FOR PERFORMANCE OPTIMIZATION? in Application Development and Automation Discussions

HOW SHOULD MAKE A SINGLE QUERY FOR PERFORMANCE OPTIMIZATION?

Former Member — Fri, 17 Aug 2007 08:14:13 GMT

GV_VAR1 = 0.

GV_VAR2 = 0.

SELECT SINGLE MATNR INTO GV_VAR1 FROM MARA WHERE MATNR = WA_ITAB-MATNR.

IF SY-SUBRC EQ 0.

SELECT SINGLE BISMT INTO GV_VAR2 FROM MARA WHERE BISMT = WA_ITAB-MATNR_O

AND MATNR = WA_ITAB-MATNR.

IF SY-SUBRC NE 0.

GV_COUNTER = GV_COUNTER + 1. " to check how many records updated successfully.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

TCODE = 'MM02'

TABLES

DYNPROTAB = T_BDCTAB

SKIP.

WRITE:/ WA_ITAB-MATNR.

WRITE:'AND '.

WRITE: WA_ITAB-MATNR_O .

WRITE:'RECORD UPDATED SUCCESSFULLY'.

ELSE.

CONCATENATE 'Old Material number already present for Material' WA_ITAB-MATNR_O INTO GV_STRING SEPARATED BY SPACE.

SKIP.

WRITE : / GV_STRING .

ENDIF. " FOR BISMT

ELSE.

CONCATENATE WA_ITAB-MATNR ' IS NOT VALID MATERIAL' INTO GV_STRING SEPARATED BY SPACE.

SKIP.

WRITE : / GV_STRING .

ENDIF. " FOR MATNR

ENDLOOP.

I WANT WRITE THESE BOTH QUERIES IN A SINGLE QUERY FOR PERFOMANCE OPTIMAZATIONS???

Re: HOW SHOULD MAKE A SINGLE QUERY FOR PERFORMANCE OPTIMIZATION?

kiran_k8 — Fri, 17 Aug 2007 08:34:34 GMT

San,

It is not the no.of queries but the way you write the queries optimises the performance.See to that in the where clause of all the select queries you give either primary key field or index field from the table.If you take care of this single step it will enhance the performance to a great extent.

K.Kiran.

Re: HOW SHOULD MAKE A SINGLE QUERY FOR PERFORMANCE OPTIMIZATION?

varma_narayana — Fri, 17 Aug 2007 09:06:16 GMT

Hi..

This is the Better way....instead of writing 2 Select statements.

GV_VAR1 = 0.

GV_VAR2 = 0.

SELECT SINGLE MATNR INTO GV_VAR1 FROM MARA WHERE MATNR = WA_ITAB-MATNR.

IF SY-SUBRC EQ 0.

SELECT SINGLE MATNR BISMT INTO (GV_VAR1,GV_VAR2) FROM MARA WHERE MATNR = WA_ITAB-MATNR

AND BISMT = WA_ITAB-MATNR_O.

IF SY-SUBRC NE 0.

<b>Reward if Helpful</b>

Re: HOW SHOULD MAKE A SINGLE QUERY FOR PERFORMANCE OPTIMIZATION?

Former Member — Fri, 17 Aug 2007 09:11:26 GMT

GV_VAR1 = 0.

GV_VAR2 = 0.

select MATNR BISMT

FROM MARA

INTO (GV_VAR1,GV_VAR2)

up to 1 rows

WHERE MATNR = WA_ITAB-MATNR

and BISMT = WA_ITAB-MATNR_O .

endselect.

IF SY-SUBRC EQ 0.

IF GV_VAR2 is initial.

GV_COUNTER = GV_COUNTER + 1. " to check how many records updated successfully.

CALL FUNCTION 'BDC_INSERT'

EXPORTING

TCODE = 'MM02'

TABLES

DYNPROTAB = T_BDCTAB

SKIP.

WRITE:/ WA_ITAB-MATNR.

WRITE:'AND '.

WRITE: WA_ITAB-MATNR_O .

WRITE:'RECORD UPDATED SUCCESSFULLY'.

ELSE.

GV_VAR2 is not initial.

CONCATENATE 'Old Material number already present for Material' WA_ITAB-MATNR_O INTO GV_STRING SEPARATED BY SPACE.

SKIP.

WRITE : / GV_STRING .

ENDIF. " FOR BISMT

ELSE.

*Sy-subrc fails

CONCATENATE WA_ITAB-MATNR ' IS NOT VALID MATERIAL' INTO GV_STRING SEPARATED BY SPACE.

SKIP.

WRITE : / GV_STRING .

ENDIF. " FOR MATNR

POINT if helpful.