topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Mon, 24 Sep 2007 21:45:12 GMT

Hi guyz,

I have to create a Z-report and it requires data to be fetched from almost 14 differents sap tables, and in final output there are around 22 columns in it. But the performance will be the big challenge, because this report have a massive data. So what you guyz recommend the technical desgin...should i create some views e.g 2 views and use them in program. Will it improves the performance.

What you guyz recommend....??

If you have some other idea for improving the performance, pls share it with me..

Thanks,

Message was edited by:

Sal Khan

Message was edited by:

Alvaro Tejada Galindo

Re: Performance issue

Former Member — Mon, 24 Sep 2007 21:49:41 GMT

From the SQL viewpoint, all you have to do is make sure that you make efficient use of indexes when retrieving data. You can retrieve data from many tables very quickly if you just keep this in mind.

From the ABAP viewpoint, avoid nested loops.

Rob

Re: Performance issue

Former Member — Mon, 24 Sep 2007 21:50:10 GMT

If you can...Create some views...also...


* Do not use SELECT-ENDSELECT.
* Use field-symbols.
* Use complete keys and index for DB reading.
* Use sorted tables when possible.

Greetings,

Blag.

Re: Performance issue

Former Member — Tue, 25 Sep 2007 13:29:15 GMT

Pls tell me whether Views will make my report effecient?

Re: Performance issue

Former Member — Tue, 25 Sep 2007 13:37:22 GMT

Hi Sal,

If in case the data to be fetched from all the 14 tables are present in any of the Logcal database like PSJ etc.,then attaching a logical data base to your customised report would be the best choice as it includes all the data fetching statements according to the Hierarchy of the tables defined in the Structure defined in LDB.You can have a look at different LDBs in the T-code SE36 and find out if any of the LDB suffice the requirement.

You can also defne your own selection screen in addition to the selection screen defined in the LDB and it becomes very handy to use in case the requirements changes at a later stage.

In case you have any further clarifications,do let me know.

Regards,

Puneet Jhari.

Re: Performance issue

Former Member — Tue, 25 Sep 2007 14:04:04 GMT

Views may give a very samll performance improvement because you may end up pulling less data. But if you follow the points I posted above, you'll do much better.

Rob

Re: Performance issue

Former Member — Tue, 25 Sep 2007 16:42:19 GMT

Hi Rob,

U mean i need to create secondary index for my select stmts? This is what you mean by proper indexing?

Re: Performance issue

Former Member — Tue, 25 Sep 2007 16:44:04 GMT

No - I meant try to use the ones that are there.

Rob

Re: Performance issue

Former Member — Tue, 25 Sep 2007 16:59:40 GMT

Hi Rob,

U mean i need to create secondary index for my select stmts? This is what you mean by proper indexing?

Puneet,

No logical database exists becuase it has some customized tables also.

Re: Performance issue

Former Member — Tue, 25 Sep 2007 17:00:51 GMT

But u r talking about sec index?

Re: Performance issue

Former Member — Tue, 25 Sep 2007 17:13:18 GMT

While extracting data using SELECT make sure that you pass all primary keys so the runtime will use the primary index which will fasten the process. If primary keys are not available then check Secondary indexes present for the database tables. If you can make use of any of these secondary indexes in your WHERE clause, that will improve the performance. You need to explicitly mention which index to be used while extracting data in the SELECT statement.

For eg

SELECT bukrs hkont gjahr belnr buzei

monat dmbtr kostl prctr xref3 shkzg budat bldat

FROM bsis

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE bukrs IN bukrs

AND hkont IN hkont

AND gjahr EQ gjahr

AND monat EQ monat

AND prctr IN prctr

AND kostl IN s_kostl

%_hints oracle 'INDEX("BSIS~XXX")'.

Here XXX is secondary index for fields BUKRS HKONT GJAHR MONAT PRCTR & KOSTL.

Hope this will help you out.

Re: Performance issue

Former Member — Tue, 25 Sep 2007 17:14:33 GMT

Primary or secondary - use whatever is available, but unless they're custom tables, I would not be creating secondary indexes for this one report.

Rob