topic Re: ST05 - Explain SQL in Application Development and Automation Discussions

ST05 - Explain SQL

Former Member — Mon, 25 Jul 2005 10:19:32 GMT

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

Re: ST05 - Explain SQL

jayanthi_jayaraman — Mon, 25 Jul 2005 10:27:21 GMT

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

Re: ST05 - Explain SQL

Former Member — Mon, 25 Jul 2005 10:31:40 GMT

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

Re: ST05 - Explain SQL

Former Member — Mon, 25 Jul 2005 10:33:20 GMT

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.

Re: ST05 - Explain SQL

jayanthi_jayaraman — Mon, 25 Jul 2005 10:39:45 GMT

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

Re: ST05 - Explain SQL

Former Member — Mon, 25 Jul 2005 10:43:55 GMT

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.

Re: ST05 - Explain SQL

Former Member — Mon, 25 Jul 2005 11:12:45 GMT

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

Re: ST05 - Explain SQL

jayanthi_jayaraman — Mon, 25 Jul 2005 11:33:51 GMT

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

Re: ST05 - Explain SQL

jayanthi_jayaraman — Mon, 25 Jul 2005 11:57:17 GMT

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

Re: ST05 - Explain SQL

christian_wohlfahrt — Mon, 25 Jul 2005 13:59:08 GMT

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