Is there any way to optimize the below query...

[former_member1249402]

SELECT  LIKP~VBELN

            LIKP~ERNAM

            LIKP~ERDAT

            LIKP~BZIRK

            LIKP~VSTEL

            LIKP~VKORG

            LIKP~WADAT

            LIKP~LFART

            LIKP~KUNNR

            LIKP~BLDAT

            LIPS~POSNR

            LIPS~PSTYV

            LIPS~VKBUR

            LIPS~MATNR

            LIPS~MATKL

            LIPS~LGORT

            LIPS~MTART

            LIPS~CHARG

            LIPS~LFIMG

            LIPS~VGBEL

            LIPS~UECHA

            LIPS~VTWEG

            LIPS~SPART

            LIPS~BWART

        INTO TABLE IT_TAB

        FROM LIKP INNER JOIN LIPS  ON LIKP~VBELN = LIPS~VBELN

        WHERE LIPS~VBELN IN S_VBELN

        AND LIKP~ERDAT IN S_ERDAT

        AND VSTEL IN S_WERKS

        AND VKORG IN S_VKORG

        AND LFART IN  ('LF','LR','NL','ZLF','ZCOD','ZSPB','ZCLR','ZRE1')

        AND LIKP~KUNNR IN S_KUNNR

        AND BLDAT IN S_BLDAT

        AND LIPS~VKBUR IN S_VKBUR

*       AND LIPS~BWART NOT IN ('602','656')

        AND MATNR IN S_MATNR

        AND MATKL IN S_MATKL

        AND LGORT IN S_LGORT

        AND MTART IN S_MTART

        AND CHARG IN S_CHARG

        AND VTWEG IN S_VTWEG

        AND SPART IN S_SPART.

[kesavadas_thekkillath]

This message was moderated.

[Former Member]

Hi Ravi,

In SELECT query, please use the same order for fields as given in the database.

For eg, LIKP~LFART should come ahead of LIKP~WADAT.

You have wisely commented LIPS~BWART NOT IN ('602','656'). Never use this. Instead delete these movement types from internal table after selecting from database.

Thanks,

Manikandan JN.

[Former Member]

Manikandan - unfortunately, both of these suggestions are incorrect. The order the fields are taken must correspond to the order they appear in the internal table.

The 'NOT IN' is not for a key field and will not impact performance. Actually, it will probably be slower than deleting them after selecting them.

There are plenty of examples of this in the SCN spaces.

Rob

[former_member194613]

no there is no way to improve the SELECT statement.

Please understand that IN-clauses are a kind of dynamic coding, but performance is only determined on the actually filled IN-clauses. You can get fast and slow combinations and there are lots of combinations.

FOR ALL ENTRIES recommendation is nonsense in this context, the join is much more flexible.

Leave the NOT IN, it has an impact on performance. I do not know your data distribution, but assume, that 90% of the records have these two values, then this will reduce the amount of transferred data dramatically (index is not the only point!).

Check SQL trace and do not look at the source code but on the actual statement send to the database and then come back.

Siegfried 

[yuri_ziryukin]

The only useful advice in the whole thread.

[Former Member]

Well, depending on the content of the dynamic parameters like S_MATNR, and the general number of entries, it may be worth to try to use the VLPMA index table.

[Former Member]

Hi,

use below view

WB2_V_LIKP_LIPS.

Regards,

Venkat.

[raymond_giuseppi]

- Did you perform an SQL trace and did you analyze it -> The SQL Trace (ST05) – Quick and Easy

- Are they some "obligatory" parameters in the selection criteria, else only "generic" hints could be provided, and you better start reading the reference documents on performance at Get started with ABAP Testing and Troubleshooting before posting. (or  be ready start to code a case/when/select/when/select/encase to optimize for multiple case of criteria actually filled, may, sometimes, give better performance, but will always result in loss of program maintainability)

IMHO, FOR ALL ENTRIES should only be used when

- we have no choice (e.g. pool/cluster type table like BSEG/RFBLG)

- we expect one of the query to give a small set of entries, so using a FOR ALL ENTRIES in subsequent select will not impair performance.

Regards,

Raymond

[former_member222875]

It depends on few things:

Performance of your select query depends on the indexes in your system on these 2 tables and also what fields in the Where condition would be filled.

1. What fields are indexed in tables LIKP and LIPS. ?

2. Out of the so many fields in the where condition what fields would be filled with values?

3. Depending on the indexed fields you can maybe make those fields in your selection screen obligatory. For example plant or sales org can be mabe mandatory.

4. Make sure that all(or most) of the fields in the index of these tables are always filled in the selection screen.

For example let us assume that there is an index on LIKP on fields ERDAT and  VKORG, then make sure that your selection screen fields S_ERDAT and S_VKORG are always populated.