Query Optimization?

[koushikking]

Hi All,

I have simple queries like following.

SELECT VBELN

              POSNR

              MATNR

              ERDAT

         INTO TABLE  LT_VBRP_OR

         FROM  VBRP

         FOR ALL ENTRIES IN LT_POPIN_N

         WHERE MATNR EQ LT_POPIN_N-MATNR.

     SELECT VBELN

                   POSNR 

                   MATNR

                   AEDAT

         INTO TABLE LT_LIPS_OR

         FROM LIPS

         FOR ALL ENTRIES IN LT_POPIN_N

         WHERE MATNR EQ LT_POPIN_N-MATNR.

each query taking about 20 seconds to process. LT_POPIN_N may contain maximum of 5 records.

Am facing too much performance problem. Please anyone guide me in an optimized way.

[Ajit_K_Panda · Accepted solution]

Hi koushik,

Please check for secondary indexes in the table and try to make the where condition bases on secondary indexes or primary key fields. You can also create secondary indexes as needed in the tables.

Regards,
Ajit

[koushikking · Accepted solution]

Hi Ajit,

Is this right way to do?

SELECT VBELN

            POSNR 

            MATNR

            AEDAT

         INTO TABLE LT_LIPS_OR

         FROM LIPS

         FOR ALL ENTRIES IN LT_POPIN_N

         WHERE MATNR EQ LT_POPIN_N-MATNR

         %_HINTS ORACLE 'INDEX("LIPS" "LIPS~MAT")'.

[Ajit_K_Panda · Accepted solution]

Hi Koushik,

You dont need to mention secondary index. it will be automatically be taken up by SAP.

SELECT   VBELN

                   POSNR

                   MATNR

                   AEDAT

      INTO   TABLE LT_LIPS_OR

      FROM LIPS

      FOR     ALL ENTRIES IN LT_POPIN_N

      WHERE MATNR EQ LT_POPIN_N-MATNR.

This should work.