Having performance issue in the query

[former_member150968]

Hi All,

We are facing performance issue with below query, Is there any way we can improve below query.

SELECT aufk~aufnr

           aufk~ktext

           aufk~objnr

           jest~inact

      FROM aufk

     INNER JOIN jest ON aufk~mandt EQ jest~mandt

       AND aufk~objnr EQ jest~objnr

      INTO TABLE ts_aufk_jest

        UP TO c_100 ROWS

     WHERE aufnr   LIKE  w_aufnr

       AND aufk~bukrs EQ us_BUKRS

       AND ( aufk~phas0 EQ c_x

        OR aufk~phas1 EQ c_x )

       AND aufk~phas3 NE c_x

       AND aufk~loekz EQ space

       AND jest~stat  EQ c_stat.

Both the tables are having records in millions.

Regards,

Manish Sethia

[ThomasZloch]

Well, top key field AUFK-AUFNR appears in the WHERE-condition, but with a LIKE operator. This can mean anything, so what is inside W_AUFNR when the query is reached? The more generic your pattern is, especially with leading wildcards, the more records must be scanned to find your result.

Thomas

[former_member150968]

Hi Thomas,

Thanks for the reply. Most of the times user does not enter any thing for 'AUFNR' and it is blank. In that case when I saw the trace, I found that SAP treats it as 'aufnr = %%'. Could you please let me know if it will have an impact because I am not sure if in that case, it will search the whole table or not.

Regards,

Manish Sethia

[ThomasZloch]

That's the problem then, no value in AUFNR means that all rows must be scanned. Since your other WHERE-conditions do not seem to be selective at first glance, I don't have a simple suggestion.

Maybe you can revisit the design and come up with additional fields in AUFK that you can declare as mandatory selection criteria. Look at the fields in the already available secondary indexes, or create your own one if all else fails.

Thomas

[former_member150968]

Hi Thomas,

We have a secondary index on the table but that index does not contain AUFNR. Will writing two separate queries help in this case? Can we check if AUFNR is not initial then execute the query with AUFNR also else a separate query which will not have AUFNR.

Regards,

Manish Sethia

[yuri_ziryukin]

Such an answer again! How many times do we have to tell that splitting a join into two SELECTs with FAE will not bring an improvement in general.

[Former Member]

Yuri Ziryukin wrote:
 How many times do we have to tell that splitting a join into two SELECTs with FAE will not bring an improvement in general.

N + 1 times.

Rob

[yuri_ziryukin]

Manish,

1. How many entries do you get back?

2. It seems that you are interested in orders in a certain state. You use both phas0/1/3 flags for the selection and STAT field from JEST table. Do you really need to check that status? What status is that: user or system (Exxxx or Ixxxx)? Why don't you include INACT = ' ' into the WHERE clause and get rid of that in the list of selected fields?

I looked in my test system. Only one PHAS flag was always set (either PHAS0 or PHAS1 or PHAS2 or PHAS3). Never two of them together. So I think you can throw out PHAS3 NE X out of your WHERE.

As there is no standard index for PHAS0/PHAS1 fields, I guess that the first selection from AUFK takes pretty long. "aufnr   LIKE  w_aufnr" does not seem to be selective in your case. So finally the phase of the order is the most selective criteria in your case. Maybe you can confirm that by playing with SE16 a bit.

The solution might be to create two (i think this is necessary) indexes with fields:

MANDT, BUKRS, PHAS0, AUFNR and MANDT, BUKRS, PHAS1, AUFNR.

Ofc this depends on whether my assumptions above are correct or not.

Regards,

  Yuri

[former_member150968]

Hi Yuri,

Thanks for your suggestions. There is already an index implemented in the table but one of the fields in where clause is not present in the index so I am not sure if that index will work or we will need to create a new index.

We have explicitly mentioned hints in the query now so could you please let me know if it will improve performance.

Also, while working on this, I got a query that how 'UP TO N ROWS' will work in case of join. I came up with two scenarios.

1.) It will take n records from table A which are satisfying where clause and put it in table B and give us the satisfying number of records.

2.) It will take all the satisfying records from table 'A' and put it in table 'B'. Get the satisfying records and return 'N' records.

I think it should follow second scenario as it does in normal select query but I am not sure.

Regards,

Manish Sethia

[yuri_ziryukin]

Manish, you did not answer any of my questions. It is quite difficult of optimize your join without modifying it.

Please come back to my original questions and provide me with answers.

[former_member150968]

Hi Yuri,

I checked in one of the quality box. I am getting 46 entries in the internal table. I am not authorized to check it in PR but when I check number of entries in tables aufk and jest, they are in millions.

For your second question, STAT starts with I but I am not sure if I can remove that inact from the select and put it in where clause because it is already in PR and functionality is working fine.

Regards,

Manish

[yuri_ziryukin]

Hello Manish,

definitely testing is required after the coding is changed. This should be a part of the standard process.

"I" in the beginning means that internal system status is checked. It may happen that this status is synchronous with the PHAS0, PHAS1, PHAS2 or PHAS3 flag. You need to check that.

I give you a tip:

Open report /SSA/CD1. Enter the following data:

And execute.

This should bring you the list of orders with your system status and show corresponding PHAS flags.

This way you can determine if PHAS flag can replace your status selection. And if it is the fact, you can get rid of the JOIN and select the data directly from AUFK.

Yuri

[Former Member]

HI,

Please explain what exectly u r looking for. if u want status message of process order then use function module STATUS_TEXT_EDIT.

Regards.