topic Fine tuning in Application Development and Automation Discussions

Fine tuning

Former Member — Thu, 10 Nov 2005 05:12:21 GMT

Hi,

i face tough job in my client place.

My client often complains abot the Z - reports being terribly slow.

For example i created a report called SALES TAX REGISTER which takes hell lot ot time for 2 consecutive days (but even the data is very huge)itself. So i have to forcibly run the report in background in the spool. Which client is not happy with.

My query how does standard reports execute so fast.

in my report although i have one or two nested loops but it is necessary.

Will O.O.P concept help me in this problem.

Pls suggest me.

regards

Deepak

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:17:46 GMT

Performance improving is a big topic but I will give some suggestions which will help you.

1. Try to remove for nested selects.

2. try to remove loops in side loops

3. try to remove for all entries(if it has lot of data)

4. tye to use as for as possible ranges. like populating into ranges and use IN in select

5. try to use joins first table should be header and join on item teble like select .... from vbak join on vbap.

7. See runtime analysis and sql trace you will get some more ideas.

Don't think that standard programs will always be faster, some times they also slow.

Cheers,

Satya

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:18:03 GMT

Deepak,

You will have to

1. Take a look at your SELECT statements and see if indexes have been used or not.

2. Usage of Loops, are you firing SELECTS with in the loops, which will definitely lot more time.

3. What are the types of internal tables that you are using. HASHED tables / SORTED tables will come in handy in these situations.

4. Are you using Joins in your SELECT statements.

OOP - I don't think it can help you in this case.

Regards,

Ravi

Note : Please reward the posts that help you.

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:18:48 GMT

Hi Deepak,

If possible can you post the code that you are referring to? It will be easier to pick out the performance problems quickly that way.

Quick Tips:

1. Avoid SELECT-ENDSELECT, especially the NESTED ones.

2. Avoid LOOP-ENDLOOP, again, the NESTED ones.

3. Select exact data from table using primary keys or the secondary indexes. Ask your DBA for checking the table statistics (if you are on Oracle 9.x & on CBO).

4. See if READ statements can be cleanly re-written using READ..BINARY SEARCH additions. (Do not forget to SORT these internal tables before using the READ-BINARY)!!

Hope this helps.

Thanks,

Chaps.

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:23:12 GMT

The following may help :

1. remove nested select statements and nested loops(Loop in a loop).

2.Always select from header and then item same as in case of Table Joins.

3.Do run time analysis and SQL trace.

path ~~> Menubar~~>System-Utilities--Performence trace

SQL trace will help and it will provide you some coding.

Message was edited by: Lanka Murthy

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:28:38 GMT

How to make use of the SQL trace.

How to run it.

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:28:52 GMT

Adding few more points

1. Dont select inside a loop... or a loop inside a select...

2. Dont sort internal table if u r using that itab for selecting from another table(for all entries)

3. SORT before looping.

4.Use SELECT SINGLE instead of SELECT…. ENDSELECT where-ever possible also Use SELECT …INTO table rather than using SELECT … ENDSELECT

5.If Nested loops has been used, Can nested Loops be avoided? E.g. Loop… Loop… Xxxxxxxxx Endloop. Endloop.

Instead use WHILE and READ statements.

6.Free all internal tables at the end of the program

Refer this link for SQL Trace

http://www.sapdevelopment.co.uk/perform/perform_sqltrace.htm

Hope this too helps u.

Message was edited by: Judith Jessie Selvi

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 05:37:42 GMT

Deepak,

1. Go to tcode ST05.

2. Check SQL Trace ON.

3. Open another session and start SE38 or the tcode to run your report/work. Do not start this yet. Pick the variant that you wanted to run this for.

4. Toggle back to session 1 (trace screen) and click the pushbutton 'Activate Trace'. On doing so, you will notice some messages confirming the trace has been switched on for your userid.

5. Toggle back to session 2 and now run your report. Hopefully, if your test finishes quickly, the trace would have been completed. Now click the pushbutton 'Deactivate Trace'. Again, check the message in the bottom of the screen.

6. Now select the option to see the trace files. If this comes up with a popup, just click OK and move on to the list display of the trace results.

7. Many information is useful like the tables accessed, the SQL statements used, the Execution Plans for each of the SQL statements, etc. But most important of them is the functionality of Summarizing the results on Tables.

Do this (selectable from the menu) and sort the Execution time Column (Descending). You will be able to see the worst performing SQL statement and the table that gets hit!!!

I would say for more details, you should refer the Online Help.

I hope this helps you.

Thanks,

Chaps.

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 06:42:12 GMT

Hey guys

Thank you chaps

Thank you all for guiding me.

Deepak

Re: Fine tuning

Former Member — Thu, 10 Nov 2005 07:40:25 GMT

U can reward points if the posting helps u.