Optimize Query for Table S983

[Former Member]

Hi All,

I used to have the following code:

 from S983
        where
          sptag ge SALE_DATE_S
          and sptag le SALE_DATE_E
          and vtweg eq '10'
          and werks eq v_werks
          and lifnr eq TBL_REPORT-LIFNR
              matnr eq TBL_ARTICLES-MATNR

But it's slow in our production although the first 4 columns in the where clause are keys. Then i found another index in the table that contains the fields "mandt, matnr". And so i recode the above into this:

 from S983
        where
          matnr eq TBL_ARTICLES-MATNR
          and sptag ge SALE_DATE_S
          and sptag le SALE_DATE_E
          and vtweg eq '10'
          and werks eq v_werks
          and lifnr eq TBL_REPORT-LIFNR
          

I expect this should yield better result because when i query the table S983 in SE16 using only MATNR input, it was faster. But when I run the second code in debug mode in our production environment it doesn't perform at acceptable level. I think the system doesn't see the appropriate index to use. Please help.

Tnx in advance,

sid

[Former Member]

You can run a performance trace (ST05) and use its "explain" function to see which index is used.

Rob

[Former Member]

Hi Rob,

Thanks for the reply. I used ST05. I noticed that when I added the column MANDT the estimated cost dropped to 1. Should I add MANDT too in my ABAP SQL statement?

Thanks,

sid