SELECT query very slow, need suggestion.

[Former Member]

Dear all,

Below stmt was coded in my report program.

It was taking around 14 seconds in development system. The days in s_datum was just 1 or 2 days max.

But when the same was transferred to test system, it is taking almost 10 minutes, though it we gave just 1 day.

SELECT * FROM likp INTO TABLE i_likp

WHERE erdat IN s_datum AND wadat_ist > '00000000'

AND lfart <> 'EL'

AND vstel not in s_vstel.

Some of you might suggest to make SELECT query with only s_datum, but i tried it in dev system, it was taking almost 22 secs with only s_datum. I thought it could be more worse in test system so, did not move that idea.

Can some one please suggest me why it is happening so.

[Rui_Dantas]

Hi,

The difference, as I suppose you know, happens because LIKP probably has much more records in production than in the development system.

You must think what is selective in your WHERE clause:

- erdat in s_datum is selective if you are are only using one day

- wadat_ist GE '00000000' is not selective (all deliveries with goods issue fulfill that condition)

- lfart NE 'EL' probably is not selective

- vsten not in s_vstel probably is not selective

So in the end only erdat is selective. There is no index in LIKP by ERDAT, so if you really want to make it faster you would need an index by ERDAT.

Still, if you are only making one SELECT (not inside a loop) I wouldn't expect that to take more than 10 minutes.

I would measure the program with SE30 to make sure it is really the SELECT that is taking so much time (post here the results), and if it really is the select post here the explain plan.

By the way, if you need to know all goods issues for the last day I would use change pointers instead.

Hope this helps,

Rui Dantas

[brad_bohn]

Have you traced the statement? There's no standard secondary index on any date field for LIKP so you're obviously running a table scan...