performance tuning checklist

[Former Member]

hi

im designing a tool for codereview.

i would appreciate if checklist of things that i need to validate for code review could be shared.

thanks in advance.

[Former Member]

hi

im designing a tool for codereview.

i would appreciate if checklist of things that i need to validate for code review could be shared.

thanks in advance.

[Former Member]

the onlly reasonable checks which can be implemented in a tool, are already implemented in the code inspector.

All recommendations of the usual lists in the internet, like

+ use FOR ALL ENTRIES instead of joins

+ avoid into corresponding

+ avoid nested loops

+ avoid select *

+ avoid selects inside loops

are highly questionable and never always correct, some are even recommendations to the worse side.

If you ask in a performance forum, then I can only recommend you review the execution not the coding!

The tools for the execution review are available. Use the code inspector for the rest.

Siegfríed

[Former Member]

This message was moderated.

[Former Member]

Hi,

These statements I would consider as highly questionable regarding performance.

> In SELECT statement, only the required fields are selected in the same order as they reside on the database table/structure/view

for SELECT list order isn't important, number of fields and selectivity in the WHERE clause is.

> No SELECT * is used

it contradicts with the 1st statement, isn't it?

> Wild cards like u2018A%u2019 is avoided as much as possible

depends: a proper Index range scan will allow for wild cards of type 'A%' ,

an index skip scan access even for '%A' if you have other WHERE fields with it in the index

>      
> Nested Select is not used instead u201CInner Joinu201D and/or u201CFor all Entriesu201D is used. u201CFor all Entriesu201D is to be used over u201CLoop at ITAB / Select / ENDLOOPu201D (FOR ALL ENTRIES retrieves a unique result set so ensure you retrieve the full key from the database)

Best .............................................> worst

JOIN -> NESTED SELECT -> FAE -> LOOP

>      
> When creating joins over database tables there should be an index at least on the inner table for the fields in the join condition else use u201C FOR ALL ENTRIESu201D select statement

The other way round it makes sense: To have no index is better in Join than in FAE. A join scans only once , an FAE table scans n-entries times if no index is availbale. So better have an index in place for BOTH.

>      
> Usage of JOIN is limited to a maximum of 2 i.e. not more than 3 database tables are joined at one time

Think of real complex data models (Amazon, ebay, ...SAP ) do you think they join only 2 tables

to get their data ?

>      
>      
> Avoid the aggregate (Count, Max, Min) functions in the database selection

databases are made for this! Think of data buffering - not every time data has to be read from disk

bye

yk

[Former Member]

@Flavya

there is something strange in your answer, the link which you provide goes to a presentation of Uli Marquard, presented at the TechEd. These recommendations I would fully support.

The rest what you write comes from other MUCH MORE QUESTIONABLE resources, which I would not trust. Yukon Kid has picked some already.

The link alone whould be a much better answer than the combination.

Siegfried

[ThomasZloch]

> The rest what you write comes from other MUCH MORE QUESTIONABLE resources, which I would not trust.

Source is here

<sorry can't get the link through the content filter...will report as copy/paste>

Thomas

[Former Member]

The website where it is copy&pasted from has been spaming SDN in the past, so it is blocked by the content filters.

@ Flavya: I am getting tired of rejecting your posts for being copied or simply wrong. This is your final warning => read the rules, or you will be banned from the site and your user ID deleted.

[Former Member]

Hi,

Use below:

Avoid using Select * in the SQL query, select required fields only.

Kindly check the Order of selected fields and Order of where clause in SQL query

Avoid using SELECT within the LOOP statements. Instead, use the READ statement.

Use JOINs instead of NESTED SELECTS

Instead of using WHERE clause in LOOP or READ, try to use it in SELECT itself.

Use Up To 1 rows instead of SELECT SINGLE if all the key fields are not available in the where clause

Use BINARY SEARCH for reading table

Sort internal table by fields for Read with binary search having where clause

Check internal table is not empty before using For All Entries in Internal table

Sort the table with fields before using Delete adjacent duplicates with comparing option

SORT should be used with BY option

DELETE ADJACENT DUPLICATE should be used with COMPARING option

Check whether Parameter and Select Option of the selection screen are used in the program

Modifying components of an Internal table

Use SELECT <fields> INTO TABLE <internal table> instead of SELECT <fields> INTO CORRESPONDING FIELDS OF TABLE <internal table>

Thanks & Regards,

Krishna....

[Former Member]

> Avoid using Select * in the SQL query, select required fields only.

Reduce by factor of two at least or forget it

> Kindly check the Order of selected fields and Order of where clause in SQL query

better leave earlier in the evening,

> Avoid using SELECT within the LOOP statements. Instead, use the READ statement.

o.k, but there is one exception SINGLE RECORD BUFFER tables

> Use JOINs instead of NESTED SELECTS

yes

>Instead of using WHERE clause in LOOP or READ, try to use it in SELECT itself.

>Use Up To 1 rows instead of SELECT SINGLE if all the key fields are not available in the where clause

o.k., performance unchanged, but can be understood easier

> Use BINARY SEARCH for reading table

actually use SORTED tables if possible, if not use standard tables with BINARY SEARCH

> Sort internal table by fields for Read with binary search having where clause

must be done,

> Check internal table is not empty before using For All Entries in Internal table

always

> Sort the table with fields before using Delete adjacent duplicates with comparing option

optional

> SORT should be used with BY option

actually there is default key which can be o.k.

DELETE ADJACENT DUPLICATE should be used with COMPARING option

Check whether Parameter and Select Option of the selection screen are used in the program

Modifying components of an Internal table

> Use SELECT <fields> INTO TABLE <internal table> instead of SELECT <fields> INTO

> CORRESPONDING FIELDS OF TABLE <internal table>

not comparable, actually does not fit to 1 recommendation,

INTO CORRESPONDING is absolutely no performance problem, it can be used if required.

Most of these recommendations will actually not change the performance which you can really see,

they change the measured time.

+ The mayor impact comes from slow executions .... no or wrong index!

+ To many records read .... wrong WHERE condition, empty FOR ALL ENTRIES

+ Poor usage of buffered tables

+ Many identical executions

+ Slow processing of internal tables, especially in nested processing

Unfortunately there not so easy to check in the coding, but what can be done is in the code inspector.

The rest must be done with the traces on execution.

Siegfried

[matt]

>

> > Use BINARY SEARCH for reading table
> actually use SORTED tables if possible, if not use standard tables with BINARY SEARCH

Unless there's only a few records, or the key isn't unique, I always use a HASHED table. I recently switched a program that was using SORTED to using HASHED for a few thousand records in some internal tables being used as buffers, and it was about twice as fast. Even though most of the actual reads were not using the entire table key.

matt

[Former Member]

thanks alot

[Former Member]

This message was moderated.

[Former Member]

> Even though most of the actual reads were not using the entire table key.

don't understand that, the key of the HASHED table must be unique. And the hashed read is only fast, if you use the unique key, otherwise it will scan the table sequentially. It will even scan the table, if

your key is something:

GUID, field1

where GUID alone is already unique. If the unique key is defined by the two fields, a read with GUID alone will be slow.

I would expect Hashed tables to be twice as fast only if the table has 5000 or more lines, then also the logarithmic time dependence of the SORTED table grows very very slowly. Twice as fast is o.k., but not the issue, a standard table without binary search would be several orders slower.

Measurements on internal tables: Reads and Loops:

Siegfried

[Former Member]

use for all entries rather than inner join,

dont use nested use

also avoid select single and select *

use parrel processing method

dont give lots of where condition some times it also improved the program load