ST05 - Explain SQL

[Former Member]

Hi all,

I'm using version 4.6C and I want to fine tune my SQL using the Explain SQL function, however I am having difficulty understanding what syntax to use in the tool.

Effectively I want to understand more about the performance of the following statement:-

select cpident tabkey

from bdcps

join on bdcp

where bdcps_{cpident = bdcp}cpident

where bdcps~mestype = 'XXXXXXXX'

and bdcps~process = ' '.

However ST05 doesn't seem to understand what I am typing and keeps giving me an error, saying 'Change update/delete to select if possible', what format of the above statement would ST05 recognise?

Thanks in advance,

Stephen

[jayanthi_jayaraman]

Hi,

Your select statement should be:

Here itab should be declared as internal table holding the two fields mentioned in select.

select cpident tabkey

from bdcps

join bdcp into table itab

on bdcps_{cpident = bdcp}cpident

where bdcps~mestype = 'XXXXXXXX'

and bdcps~process = ' '.

[Former Member]

Thanks Jayanthi,

However, I really want to know how I would enter this statement in the the ST05 Explain SQL statement transaction (as this would help me in the future for other queries), I just can't get ST05 to understand my query.

Regards

Stephen

[jayanthi_jayaraman]

Hi,

step 1:Create a ABAP Program.

Step 2:Activate the program without errors.

Step 3:Go to ST05 Transaction

Step 4:Check mark SQL Trace.

Step 5:Click Trace on

Step 6:Execute the program

Step 7:Trace off

Step 8:List trace

[Former Member]

select a_{cpident a}tabkey

into table i_tab

from bdcps as a

inner join bdcp as b

ON a_{cpident = b}cpident

where a~mestype = 'XXXXXXXX'

and a~process = ' '.

Try this one. Ur Select query was wrong.

SQL Trace

SQL trace(ST05) provides the developer with the ability to analyse database select statements. Simply execute ST05

to turn on SQL trace, then execute the statement/program you want to analyse. Now turn off SQL trace using ST05

and click on list trace to view the details.

You can also perform traces on other items such as authorisation objects.

http://www.sapdevelopment.co.uk/security/authority/authority_authtrace.htm

Hope this helps.

[Former Member]

Thanks Judith,

However ST05 doesn't like this syntax either:-

select a_{cpident a}tabkey

into table i_tab

from bdcps as a

inner join bdcp as b

ON a_{cpident = b}cpident

where a~mestype = 'XXXXXXXX'

and a~process = ' '.

Just for clarification, I am using ST05, hitting the 'Explain one SQL request' button, this then takes me to a screen where I can enter a specific statement. I want to learn how to use this transaction, rather than having to create an ABAP program each time. It would be handy to get an immediate verdict on what my statement will do in the system.

[Former Member]

http://www.sapdevelopment.co.uk/perform/perform_sqltrace.htm In this link it is mentioned u can <b>execute the statement/program you want to analyse</b>.

Just check this out.

Just to add, u cant run the trace without any program it will work together when u run a report.

Make sure to turn off the trace or else it will run and slow down the system performance.

So u cant run for an individual slect, u have to write a program and run.

Hope u got it.

If u have solved ur problem kindly reward and close the thread.

Message was edited by: Judith Jessie Selvi

[jayanthi_jayaraman]

Hi,

Check this.

http://help.sap.com/saphelp_nw04/helpdata/en/17/358df9c9fee2469105731e10756921/content.htm

http://help.sap.com/saphelp_webas610/helpdata/en/d1/801f89454211d189710000e8322d00/content.htm

You can also use SE30 to analyze performance.

<b>To know the usuage of Explain one SQL statement,just follow the link.

http://help.sap.com/saphelp_bw30b/helpdata/en/d1/801fd7454211d189710000e8322d00/content.htm</b>;

Message was edited by: Jayanthi Jayaraman

[jayanthi_jayaraman]

Hi,

Go to ST05->Explain one SQL statement.

Type in Uppercase.

SELECT * FROM MARA

No fullstop should be given.

Then click Explain.

You can see the trace.

Hope this solves your problem.If so,kindly close the thread by rewarding points.Otherwise,get back.

Did you tried?

Message was edited by: Jayanthi Jayaraman

[christian_wohlfahrt]

Hi Stephen,

something out of topic:

use view BDCPV instead of a join. That's the common way and you might profit from buffering.

Regards,

Christian