Performance Issue with Select Statement

[Former Member]

I have a performance issue with a SELECT statement that is within code that I'm working on:

SELECT MANDT MATNR WERKS LGORT LABST

       SPEME INSME

       INTO TABLE INT_MARD1

       FROM MARD

       CLIENT SPECIFIED

       WHERE MATNR IN S_MATNR

          AND WERKS IN S_WERKS

          AND LGORT IN S_LGORT.

I'm somewhat new to ABAP (and years from learning SQL) and having a difficult time knowing what the issue is here.

[schneidertho]

Hi Dave,

two suggestions

• make an SQL trace (ST05)
• why do you use CLIENT SPECIFIED (that should be an exception and for the use-case above I am not sure, if this makes sense)? And why do you not specify the client when using CLIENT SPECIFIED? -> my assumption would be that the system due to that does not use an index (but you can verify this with the trace)

Best regards

Thorsten

[Former Member]

Hi,

Purpose of using CLIENT SPECIFIED is: you can then address the client fields in the individual clauses of the SELECT statement.

eg: select *  into <wa> from <dtab> CLIENT SPECIFIED where MANDT = '800'.

[SuhaSaha]

Hello Janani,

Did you check the implications of using the CLIENT SPECIFIED addition before using it? Whenever in doubt make sure you use the F1 key and since you are new to ABAP that'll help a lot.

SELECT MANDT MATNR WERKS LGORT LABST
       SPEME INSME
       INTO TABLE INT_MARD1
       FROM MARD
       CLIENT SPECIFIED
       WHERE MATNR IN S_MATNR
          AND WERKS IN S_WERKS
          AND LGORT IN S_LGORT.

In your select query you've specified CLIENT SPECIFIED but have not used the MANDT field in your WHERE clause. This causes the data to be selected from all the clients & hence the performance issue.

BR,

Suhas

[Former Member]

Hi Dave,

when we use a client specified statement with select statement it specifies manual client handling that means:

when you use the client specified with select queries always remember to add mandt with where clause

to specify the client.

select (fields) from table client specified into internal table where mandt = '800'.

if this mandt field is not specified then selection will be made across all the clients.

[Former Member]

Hi Dave Fischer,

         I think the CLIENT SPECIFIED is not required in the select statement because data will be fetched from the particular client which your LOG ON into,and more over if specify CLIENT SPECIFIED in select statement then try to add WHERE clause Mandt = SY-MANDT, if you hard coded as 800 or 900 then

if Your report  is moved from development to production den you cant change the value in WHERE clause.

U can check the Run time Analysis in SE30 of the report performance and for  SQL trace analysis ST05  .

My Advise is not use Client specified .

Remove Mandt field and Client specified in select  statement it improve the performance of the statement

Thanks & regards ,

MadhuKumar

[Former Member]

Hi,

By specifying CLIENT SPECIFIED, automatic client handling is switched off. That means you have to specify client field in where clause, else it will fetch data from all the clients.

[Former Member]

Dear Dave Fischer,

                              You don't want to use CLIENT SPECIFIED so remove it and check. If you want to fetch records for all clients then no other way surely it will take sometime. Do you need to fetch all clients' records?

Cheers,

Dineshwar Singh Eswar.