Skip to Content
author's profile photo Former Member
Former Member

performance tuning checklist

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

9 Answers

  • Posted on Dec 11, 2008 at 07:49 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 11, 2008 at 11:49 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Dec 11, 2008 at 02:10 PM

    @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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Thomas Zloch

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Dec 15, 2008 at 02:43 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 15, 2008 at 02:58 PM

    > 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

    Add a comment
    10|10000 characters needed characters exceeded

    • >

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

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2008 at 03:06 PM

    thanks alot

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 09:09 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 22, 2008 at 11:37 AM

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

    Runtimes of Reads and Loops on Internal Tables

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 22, 2008 at 11:48 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.