topic Re: Performance Issue with Select Statement in Application Development and Automation Discussions

Performance Issue with Select Statement

Former Member — Mon, 27 Aug 2012 15:28:48 GMT

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.

Re: Performance Issue with Select Statement

schneidertho — Mon, 27 Aug 2012 16:33:16 GMT

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

Re: Performance Issue with Select Statement

Former Member — Tue, 28 Aug 2012 05:11:14 GMT

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'.

Re: Performance Issue with Select Statement

SuhaSaha — Tue, 28 Aug 2012 05:23:38 GMT

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

Re: Performance Issue with Select Statement

Former Member — Tue, 28 Aug 2012 09:21:45 GMT

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.

Re: Performance Issue with Select Statement

Former Member — Tue, 28 Aug 2012 11:35:39 GMT

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

Re: Performance Issue with Select Statement

Former Member — Wed, 29 Aug 2012 12:24:03 GMT

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.

Re: Performance Issue with Select Statement

Former Member — Tue, 25 Sep 2012 11:55:40 GMT

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.