topic Re: select query problem in Application Development and Automation Discussions

select query problem

ashish_gupta11 — Sun, 15 Aug 2010 11:42:31 GMT

Hi Expert ,

i have one which take more time to execute . can you please suggest me how to make it effective .

select KUNNR from knvp into CORRESPONDING FIELDS OF TABLE customer where KUNN2 = P_KUNNR AND PARVW = 'ZD' .

SORT CUSTOMER BY KUNNR.

DELETE ADJACENT DUPLICATES FROM CUSTOMER .

p_year - 1**********

IF CUSTOMER[] IS NOT INITIAL.

SELECT A_{KUNAG A}VBELN A_{FKDAT B}MATNR B_{ARKTX B}FKIMG B_{VRKME B}MEINS C~EXTWG INTO CORRESPONDING FIELDS OF TABLE IT FROM ( ( VBRK AS A INNER JOIN VBRP AS B ON

A_{VBELN = B}VBELN ) INNER JOIN MARA AS C ON B_{MATNR = C}MATNR ) FOR ALL ENTRIES IN

CUSTOMER WHERE A_{KUNAG = CUSTOMER-KUNNR AND ( B}PSTYV NE 'TANN' AND B~PSTYV NE 'RENN' )

AND A~VBTYP = 'M'

AND C~EXTWG = 'SALE_IB'

AND A~BUKRS IN S_BUKRS

AND A~FKSTO = SPACE

AND ( A~FKDAT BETWEEN CUTOFF AND CUTOFF1 ) .

ENDIF.

cutoff & cutoff1 are month variable between dates

Thanks ,

Ashish Gupta

Re: select query problem

Former Member — Sun, 15 Aug 2010 12:13:33 GMT

asish..

few suggestions...

1. Do not keep non key fields in the where clause, rather use DELETE after selection is done to remove the unwanted entries in the fetched table

2. break down the big select statement . that will help.

now try.

Re: select query problem

SuhaSaha — Sun, 15 Aug 2010 13:09:54 GMT

1. In the select you're neither utilising any key fields nor hitting any DB indexes (as a matter of fact i checked and do not find any relevant indexes defined by SAP as well) If i understand correctly you're searching for billing dates in 1 month range (correct me if i'm wrong).

B~PSTYV NE 'TANN' AND B~PSTYV NE 'RENN'

You can take this out of your SELECT & add a DELETE statement on the corresponding internal table after your SELECT.

3. You can try removing the INTO CORRESPONDING FIELDS OF addition & check if it fasten your runtime. ( I know many sceptics who would say this doesn't improve the runtimes much ! )

All said make full use of SAP Runtime Analysis Tool (SE30), compare your runtimes, tweak the SELECT

Cheers,

Suhas

Anyways good join construct (i prefer them with aliases)

Re: select query problem

ashish_gupta11 — Sun, 15 Aug 2010 13:38:18 GMT

Masters ................Thanks for replies .

yes I am searching within one month ........

your supports are valuable.

Thanks ,

Ashish