topic Re: Expensive SQL due to huge data selected in Application Development and Automation Discussions

Expensive SQL due to huge data selected

Former Member — Thu, 11 Mar 2010 03:39:53 GMT

Hi experts,

There is an expensive select query in my report. From the trace, I can see it nearly take 9 minutes or so to run.

SELECT MBLNR

MJAHR

ZEILE

INTO TABLE FP_IT_MSEG

FROM MSEG

FOR ALL ENTRIES IN FP_IT_S031

WHERE MATNR EQ FP_IT_S031-MATNR

AND

WERKS EQ FP_IT_S031-WERKS

AND

LGORT EQ FP_IT_S031-LGORT.

And about 205206 records of the 7838904 records will be selected to table FP_IT_MSEG which is declared as standard table. Although it matched an secondary index, it still took that much time.

How should I optimize it? Will it speed up if using package size(such 500) ? Should I declared table FP_IT_S031 with more initial size(like 300000) ? Or do you think I should split it into several same select statement with smaller size FP_IT_S031.?

If you faced similar issues, do let me know? I can only test it after moving to Q.

Thanks & Regards..

Re: Expensive SQL due to huge data selected

Former Member — Thu, 11 Mar 2010 10:03:13 GMT

Hi,

you are selecting large amount of data and using FOR ALL ENTRIES - system generates new query for each row in FP_IT_S031 table.

You could try to:

- descrease size of FP_IT_S031 table (this might not meet your requirements) - how many rows does it have in average?

- you could try to create RANGES for material, and select more data from database,

and than filter them and remove unnecessary - but you have to check if it works for you (it depends on how many different

materials you have, how many plants and storage locations, etc.).

Changed code would look like:


// prepare RA_MATNR based on FP_IT_S031
...

// select data
SELECT mblnr mjahr zeile matnr werks lgort
  INTO TABLE fp_it_mseg[]
  FROM mseg
  WHERE matnr IN ra_matnr[].

// filter data
LOOP AT fp_it_mseg.      // better use ASSIGNING here
  READ TABLE fp_it_s031[]
    WITH TABLE KEY matnr = fp_it_mseg-matnr
                   werks = fp_it_mseg-werks
                   lgort = fp_it_mseg-lgort
    TRANSPORTING NO FIELDS.
  IF sy-subrc NE 0.
    DELETE fp_it_mseg.
  ENDIF.
ENDLOOP.

Although it matched an secondary index, it still took that much time.

You are matching index, but FOR ALL ENTRIES generate series of queries, which takes long time

Regards,

Przemysław

Re: Expensive SQL due to huge data selected

HermannGahm — Thu, 11 Mar 2010 20:50:05 GMT

Hi,

ok, let's see:

9 minutes (or so) for 205206 is 0.002632 seconds (2.6 milli seconds) per record (or so)

That is not too shabby

double check that there are no duplicates in the internal table FP_IT_S031.

What database are you running on?

Dependent on the database you are running on there might be a few options

to improve it a little bit

e.g.

increase the blocking factor for FAE with a hint will do less round trips

and could improve the performance a little bit.

a rather drastic option would be to create the ideal index (add field form select to the used index(

in order to get an index only access but that is probably too much...

Once again, your response time is already quite good there is not much room for

improvement with an FAE.

Another option to check:

where do the records in internal table FP_IT_S031 come from? if they come straight out

of a database table avoid the FAE and use a join. Here you could probably gain more.

Kind regards,

Hermann

Re: Expensive SQL due to huge data selected

Former Member — Fri, 12 Mar 2010 10:28:14 GMT

200.000 records ... is a lot, is this really necessary???

The FAE will take a while, the recommendation with the RANGES is nonsense, it will dump!!!

If it would not dump, the reduction of the WHERE condition would easily lead to 1.000.000 to be transferred.

So pleae think twice before giving recommendations!