Performanance Issue

[Former Member]

Moderator message: Please use a more informative subject in future

Hi All There,

I am facing a problem in selecting a data from RESB table with arround 10 where clause its taking lots of time

SELECT MATNR RSNUM LGORT UMLGO BDTER BDMNG ENMNG MEINS WEMPF FROM RESB

INTO TABLE T_PENDRES WHERE WERKS = P_WERKS AND

XLOEK <> 'X' AND XWAOK = 'X' AND KZEAR <> 'X' AND BDART = 'MR' AND

BWART = W_BWART AND MATNR IN P_MATNR AND LGORT IN P_LGORT AND

UMLGO IN P_UMLGO AND BDTER >= P_FRDATE.

is there any solution for this.

Edited by: Matt on Feb 11, 2009 2:10 PM

[Former Member]

Cant you retrieve key fields to be used in the select query...

[Former Member]

Cant you retrieve key fields to be used in the select query...

[Former Member]

hi....

in that case you give the values of the key field in the select clause.....

leave other fields.

now loop at the table and check the condition inside the loop.

if you have to display it then display it if condition abou other fields satisfy....

and if you have to process it then append it to another table and use that table...

i too had same issue once..

regards

[kiran_k8]

Sagar,

Why don't you go with RSNUM and RSPOS which are the key fields.If I am not wrong you will be fetchin material document number from MSEG.Along with that fetch even rsnum from MSEG for that material document and then fetch the entries w.r.t to this RSNUM from RESB table.

K.Kiran.

[Former Member]

hi,

try this and see...

SELECT MATNR RSNUM LGORT UMLGO BDTER BDMNG ENMNG MEINS WEMPF FROM RESB

INTO TABLE T_PENDRES WHERE BDART EQ 'MR' AND

XLOEK EQ 'X' AND

XWAOK EQ 'X' AND

KZEAR EQ 'X' AND

MATNR IN P_MATNR AND

WERKS EQ P_WERKS AND

LGORT IN P_LGORT AND

BDTER GE P_FRDATE.

BWART EQ W_BWART AND

UMLGO IN P_UMLGO.

Still it is not good perfomance... try to get the fields in the same order which is in the table...

that is

select rsnum lgort....

Rgds.,

subash

[Former Member]

Hi,

Try to use index 'M' in the select query.

Then rest data you can detele from the internal table.

Regards,

Amit Kumar Singh

[viquar_iqbal]

Hi

SELECT MATNR RSNUM LGORT UMLGO BDTER BDMNG ENMNG MEINS WEMPF FROM RESB

INTO TABLE T_PENDRES

Then loop t_pendres and put an if condition inside the loop for all the conditions and if they satify let them remain and if not then delete the rest of them from the internal table

Thanks

Viquar Iqbal

[Former Member]

Hi ,

Tou write those fields of Select and Where clause in the sequence according to their sequence in db table RESB like -

---

SELECT 
        rsnum  
        matnr  
        lgort  
        bdter  
        bdmng  
        meins  
       enmng  
       umlgo  
        wempf           
FROM RESB
INTO TABLE T_PENDRES WHERE 
BDART = 'MR' AND 
XLOEK  =  'X' 
AND XWAOK = 'X' 
AND KZEAR 'X'
AND MATNR IN P_MATNR 
 AND WERKS = P_WERKS
AND LGORT IN P_LGORT
AND BDTER >= P_FRDATE.
 AND  BWART = W_BWART
 AND UMLGO IN P_UMLGO .

Atleast Maintain this above sequence in where clause .

Regards

Pinaki

Edited by: Pinaki Mukherjee on Feb 11, 2009 6:35 AM

[former_member222860]

Hi,

Follow the same sequence of fields in the where_cluase same as in the table, then the system can use the index of the table.

Just like this:

SELECT RSNUM MATNR LGORT UMLGO BDTER BDMNG ENMNG MEINS WEMPF 
  FROM RESB
  INTO TABLE T_PENDRES 
 WHERE BDART = 'MR' 
   AND XLOEK  =  'X' 
   AND XWAOK = 'X' 
   AND KZEAR 'X'
   AND MATNR IN P_MATNR 
   AND WERKS = P_WERKS
   AND LGORT IN P_LGORT
   AND BDTER >= P_FRDATE.
   AND  BWART = W_BWART
   AND UMLGO IN P_UMLGO .

thanks\

Mahesh

[Former Member]

CONSTANTS: C_X  TYPE CHAR1 VALUE 'X',
           C_MR TYPE CHAR2 VALUE 'MR'.
           
SELECT RSNUM
       RSPOS
       RSART
       BDART
       XLOEK
       XWAOK
       KZEAR
       MATNR
       WERKS
       LGORT
       BDTER
       MEINS
       ENMNG
       BWART
       UMLGO
       WEMPF
FROM RESB
INTO TABLE IT_RESB

WHERE MATNR IN P_MATNR
AND   WERKS EQ P_WERKS
AND   LGORT IN P_LGORT
AND   BWART EQ W_BWART
AND   UMLGO IN P_UMLGO.


IF SY-SUBRC EQ 0.
  DELETE IT_RESB WHERE XLOEK NE C_X.
  DELETE IT_RESB WHERE XWAOK NE C_X.
  DELETE IT_RESB WHERE KZEAR NE C_X.
  DELETE IT_RESB WHERE BDART NE C_MR.
  DELETE IT_RESB WHERE BDTER GE P_FRDATE.
  ELSE.
    MESSAGE E000 WITH 'NO DATA FOUND FOR THE SELECTION'.
ENDIF.

Hope this will help u.

Thanks & Regards

Syed.

[ThomasZloch]

Please don't follow the suggestions so far:

- order of fields in WHERE clause does not matter for index selection

- leaving out WHERE conditions to read more from database just to delete from internal table afterwards is worse than nonsense, it's counterproductive

Index RESB~M should be used since you are selecting MATNR, but it depends on the content of range P_MATNR. If you leave it blank, the system needs to scan the full table anyway.

You can run a ST05 trace to find out what's going on.

Thomas

[Sm1tje]

Thomas,

just out of pure curiosity:

 order of fields in WHERE clause does not matter for index selection 

Is this based on experience? Cause I meant to remember that somewhere in the help(.sap.com), it said that it does matter. I haven't checked it before, but I do use the exact order in which these fields are defined.

 leaving out WHERE conditions to read more from database just to delete from internal table afterwards is worse than nonsense, it's counterproductive 

I tend to disagree, maybe not in this case, but in cases in which the WHERE clause is too 'big', it does make sense to leave out some of the conditions and in the next step delete these entries from internal table. However, this is not done within the loop, but more by DELETE FROM itab WHERE.

Thanks in advance for your response. These are the discussions I really like about SCN, so hopefully you, as an experienced developer, will respond.

Gr. Micky.

[ThomasZloch]

Hi Micky,

> >order of fields in WHERE clause does not matter for index selection

>

> Is this based on experience?

Yes it is. We have an Oracle DB here, as have I guess 80% of all SAP installations. There might be exceptions for more exotic DBs, I don't know. Would like to hear about it. I have also seen comments in rather old SAP help texts which seem not up-to-date anymore. Doesn't hurt to have the fields in order, but there is no gain, imho.

> > leaving out WHERE conditions to read more from database just to delete from internal table afterwards is ... counterproductive

>

> I tend to disagree, maybe not in this case, but in cases in which the WHERE clause is too 'big', it does make sense to leave out some of the conditions and in the next step delete these entries from internal table. However, this is not done within the loop, but more by DELETE FROM itab WHERE.

I've had rare cases where it was logically necessary to delete data from internal tables that were filled from the DB before, but I really doubt it could ever be beneficial to skip applicable WHERE conditions in a SELECT. If the optimizer really trips over these conditions, I would rather use a hint to force the correct index being used (again, very rare case).

Always happy to discuss and learn, if you have a good example, please let me know.

Thomas

[Sm1tje]

Thomas,

thanks for the reply. Let me see if somehow I can verify this by doing some test on our current system. Problem will probably be the amount of data which is pretty low. But for the index it shouldn''t be a problem.

Thanks again.

[HermannGahm]

Hi Thomas,

>>order of fields in WHERE clause does not matter for index selection

>There might be exceptions for more exotic DBs, I don't know. Would like to hear about it.

So far i've never experienced plan changes by optimizer decission due to different order of

the fields in the WHERE clause on DB6, ORACLE or MSSQL. For DB2 and DB4 i've never heard

from such a thing as well. Nevertheless people keep telling that there are examples where it

makes a difference. But whenever i asked them for reproducable examples i didn't got any... .

That's at least my experience.

>I have also seen comments in rather old SAP help texts which seem not up-to-date anymore.

>Doesn't hurt to have the fields in order, but there is no gain, imho.

Looking globally at the database and the statement cache things are a little bit different:

Statements are hashed and stored there by string comparison. E.g.

SELECT * FROM dbtab WHERE field1 = 'something' and field2 = 'something_else' and field3 = 'another thing'. 
SELECT * FROM dbtab WHERE field2 = 'something_else' and field1 = 'something' and field3 = 'another thing'.
SELECT * FROM dbtab WHERE field3 = 'another something' and field2 = 'something_else' and field1 = 'something'.

will end up with 3 different statements (strings) in the statement cache. 3 different statemtents means:

- 3 times parsing, compiling (syntax and authorizatin check, building a plan)

- 3 times storing and maintaining the statment and the 3 plans (which are the same) in the cache

- ...

So globally on the database there is a difference compared to the situation when we would have had only one statement (all fields in the where condition in same order). But this difference normally doe's not play a big role in SAP systems. In general: If every developer would use the same order of fields, there would be a higher chance for reuse and little less cpu usage (for parsing, compiling) and storage requirement in the statement cache. Todays caches are big and CPUs are fast... but in ancient times caches weren't so big and CPUs weren't so fast.

Maybe that recomendation: 'Use fields in order...' comes from these times were the impact was bigger

than it is today... but nevertheless even with small caches this impact should not have been siginificant in SAP environments.

Kind regards,

Hermann

[Former Member]

>

> Hi Thomas,
> 
> So far i've never experienced plan changes by optimizer decission due to different order of
> the fields in the WHERE clause on DB6, ORACLE or MSSQL. For DB2 and DB4 i've never heard
> from such a thing as well. Nevertheless people keep telling that there are examples where it
> makes a difference. But whenever i asked them for reproducable examples i didn't got any... .
> That's at least my experience. 
> 
> >I have also seen comments in rather old SAP help texts which seem not up-to-date anymore. 
> >Doesn't hurt to have the fields in order, but there is no gain, imho.

I think this is the issue. I also recall in earlier releases that it helped to put the fields in the WHERE clause in the same order as the index. But this doesn't seem to be an issue now.

On the other hand, for reasons or program readability, I try to put the fileds in the order of the index I expect to be used.

Rob

[matt]

Please use a more informative subject in future