Queries taking too long

[Former Member]

Hi,

I have 2 selects in 2 different programs, and I wonder why it is taking much more time that I expect for it to execute.

One select is:

SELECT spmon waers werks matnr lgort labst insme retme speme trame meins verpr vprsv
    INTO CORRESPONDING FIELDS OF TABLE gt_itab
    FROM zzgmm_stockdata
    WHERE spmon = p_spmon AND
          sobkz = 'R' AND
          waers = p_curc AND
          werks EQ p_werks.

It takes about 9 minutes - table zzgmm_stockdata has 11M records.

Second select is:

SELECT matnr werks lgort sobkz labst insme speme trame retme meins verpr vprsv waers spmon maktx logbe
    INTO CORRESPONDING FIELDS OF TABLE gt_itab1
    FROM zzgmm_stockdata
    WHERE werks EQ p_werks AND
          waers = p_curc AND
          sobkz = 'R' AND
          ( spmon = p_spmon1 OR
          spmon = p_spmon2 ).

This select takes about 4 minutes.

Any idea what can cause the first select to take that time??? While as for my expectation it has to take even less than the second selectu2026

Thanks in advanced.

[Former Member]

Hi,

I have 2 selects in 2 different programs, and I wonder why it is taking much more time that I expect for it to execute.

One select is:

SELECT spmon waers werks matnr lgort labst insme retme speme trame meins verpr vprsv
    INTO CORRESPONDING FIELDS OF TABLE gt_itab
    FROM zzgmm_stockdata
    WHERE spmon = p_spmon AND
          sobkz = 'R' AND
          waers = p_curc AND
          werks EQ p_werks.

It takes about 9 minutes - table zzgmm_stockdata has 11M records.

Second select is:

SELECT matnr werks lgort sobkz labst insme speme trame retme meins verpr vprsv waers spmon maktx logbe
    INTO CORRESPONDING FIELDS OF TABLE gt_itab1
    FROM zzgmm_stockdata
    WHERE werks EQ p_werks AND
          waers = p_curc AND
          sobkz = 'R' AND
          ( spmon = p_spmon1 OR
          spmon = p_spmon2 ).

This select takes about 4 minutes.

Any idea what can cause the first select to take that time??? While as for my expectation it has to take even less than the second selectu2026

Thanks in advanced.

[valter_oliveira]

Hello there.

Since your ztable has 11M records, you have to take the key/index subject very seriously. I cannot really help you in this specific case because I don't know the structure of your table.

What's the primary key? Do you have any secondary index defined? If not, forget about good performance. You are probably dealing with full table scans.

Regards,

Valter Oliveira.

[Former Member]

The table's primary key is:

mandt matnr werks lgort lifnr kunnr vbeln posnr waers spmon

The table contains 5 NON UNIQUE indexes:

1st:

mandt matnr werks lgort waers spmon sobkz

2nd:

mandt matnr wekrs lifnr vbeln posnr waers spmon sobkz

3rd:

mandt matnr werks lgort kunnr vbeln posnr waers spmon sobkz

4th:

mandt matnr werks kunnr vbeln posnr waers spmon sobkz

5th:

mandt werks waers spmon sobkz

Any suggestions/modifications to the table's structure are welcome. It is defined like that, of course, for it accords with the definitions and the way I think it should be implemented, so far.

[valter_oliveira]

Hi again.

First of all, I would say that you have too much secondary indexes starting with the same fields. You have 1 primary key, and 4 secondary indexes starting with matnr and werks. So you have 5 indexes (primary key and 4 secondary indexes), and without matnr in the where condition, you cannot use a single one of them!

Remember that the index doesn't have to contain all fields you use in a where condition. The where condition only has to contain the first field of an index for it be a good candidate for optimizer choosing it.

Since primary key is: mandt matnr werks lgort lifnr kunnr vbeln posnr waers spmon I would propose secondary indexes of this kind.

1 - mandt werks spmon sobkz

2 - mandt lifnr spmon sobkz

3 - mandt kunnr spmon sobkz

4 - etc ...

But this may change depending of your use of this table. Could be using vbeln too ...

Now, if you have an index (the 5th) with mandt werks waers spmon sobkz, is strange that the select is taking so long. Perhaps the optimizer is not choosing it. Use ST05 to trace it. If you have any doubts, you are welcome.

Remember also, that after creating/changing indexes, you should run the optimizer statistics.

Regards,

Valter Oliveira.

Edited by: Valter Oliveira on Feb 1, 2009 4:46 PM