Performance issue

[Former Member]

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

<b><REMOVED BY MODERATOR></b>

Thanks,

Message was edited by:

Sal Khan

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

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

[former_member583013]

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.

[Former Member]

Pls tell me whether Views will make my report effecient?

[Former Member]

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

[Former Member]

Hi Rob,

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

[Former Member]

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

Rob

[Former Member]

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.

[Former Member]

But u r talking about sec index?

[Former Member]

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.

[Former Member]

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

[Former Member]

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.