Update Statement In Sap Abap Performance' title='Update Statement In Sap Abap Performance' />The SAP Community is the quickest way for users to solve problems, learn more about SAP solutions, and invent new ways to get things done. Code Inspectors Performance Checks ICode Inspector is SAPs tool for the analysis of static ABAP code, data dictionary objects and other repository objects. The tool transaction SCI has a set of predefined performance checks that can help you to improve your code so as to optimize the performance of your program. In this weblog series I want to present some of these checks, their theoretical background, and what you can do to remedy the problem when you get a message from the Code Inspector dont panic. When working with SCI you should always be aware that a simple, static check tool cannot cure all the performance or scalability problems your applications may have. Update Statement In Sap Abap Performance' title='Update Statement In Sap Abap Performance' />Code Inspector is SAPs tool for the analysis of static ABAP code, data dictionary objects and other repository objects. The tool transaction SCI has a set of. CHECK statement can be used to terminate a subroutine conditionally. If the logical expression in the CHECK statement is untrue, the subroutine is terminated, and the. ABAP4 Tuning Checklist. General Considerations 1. Is the program YEAR 2000 compliant check date logic and make sure there are not any 2 digit. ABAP Best Practices for Business Intelligence Table of Contents. How to decide whether to use Start Routines, End Routines or Individual Update Routines. If you are an experienced ABAP Developer, then you most likely know the classical performance guidelines for using Open SQL if not, then you should look them up. SAP ERP Scalability and Performance Monitoring Testing Analyzing on Microsoft Windows 2000 Reference StepbyStep Instructions Tips Tricks. SAPST12Trac111.png' alt='Update Statement In Sap Abap Performance' title='Update Statement In Sap Abap Performance' />These problems often only become visible at runtime especially when the code becomes more complex. This can be, for example, identical database accesses within one dialog step, or frequently called routines that, after having gone down several call stack levels, realize that there is nothing to do, or that do identical executions over and over again. Such problems cannot be detected by a check tool that works on the static definition of repository objects. But such a tool can help you to realize some quick wins, by means of detecting shortcomings in the implementation. So, running the Code Inspector is just one step on the way to better code, but only a fool would do without it. The performance checks I want to discuss in this weblog series in some more detail are      Articles of this series Analysis of the WHERE clause for SELECT, UPDATE and DELETE discussed in this articleCode Inspectors Performance Checks IICode Inspectors Performance Checks IIICode Inspectors Performance Checks IIIIAnalysis of the WHERE clause for SELECT, UPDATE and DELETEAn inefficient database access can significantly slow down the whole application. As a rule, all frequently executed database accesses should be supported by an appropriate database index. Even if there is an appropriate index, database accesses can fail to use this index at runtime, for example, because the database table statistics are not up to date. But these are exceptions and they are outside the scope of a static check tool such as the Code Inspector. Code Inspector only analyzes the fields of the WHERE clause and compares them to the indexes defined in the data dictionary. This, of course, only works as long as neither the database name nor the WHERE clause is defined in the code dynamically. Note Remember that business configuration tables, which are normally small, often read but rarely modified, should be buffered within SAPs table buffer on the application server. How to access such tables without loosing the advantage of buffering will be described in a subsequent weblog SELECT statements that bypass the table buffer. Check details. This is what the check does in detail A first check determines whether there is a WHERE clause at all. A SELECT without a WHERE clause does not restrict the result set or only restricts the result set to all entries in one client for a client dependent table which is a real no no with respect to performance. Then, for all normalized sub clauses of a WHERE clause the Code Inspector checks whether they contain a field that is also the first field in a database table index. Normally, databases have difficulties to fill gaps in an index, especially if the first index field is concerned. Thus, if the database index contains the fields A, B, C, it does not help to have a WHERE clause with only the condition B SHERLOCK AND C DETECTIVE. Since the first field A is missing, the database will do a full table or full index scan. Only if field A has just a few values, some database platforms might be able to fill the gap but you should not rely on this. Another thing that the Code Inspector checks is whether the index fields in the WHERE clause are compared against the current parameters with positive conditions like EQ, or BT. This check is necessary because, to the database, it is of no help to have a WHERE clause of the Type A NE HOLMES NE stands for not equal or A IS NOT NULL, or the like. Such negations always lead to a full scan. Is the accessed database table large or small Clearly, a full table scan is more harmful when it is done on a large table with thousands of entries than for a small table. The answer to this question influences the prioritization of the check messages by the Code Inspector. To determine the table size, the Code Inspector examines the value for the size category in the technical settings of the accessed table in the data dictionary. Accessing large tables size category 2 is seen more critical than accessing small ones. Counting the real number of database entries in the table would be far too slow, and so the Code Inspector does without. How to proceed with a Code Inspector message. Now what should you do when the Code Inspector tells you that a WHERE clause cannot use any of the existing indexes of a database table or that there is no WHERE clause at allFirst of all Do not create new indexes for statements which are rarely used Instead investigate the following If the statement, routine, or program with the inefficient database access is not used or needed anymore Delete it You would not believe how many unused scraps of source code accumulate in the repository over the years. So use the opportunity provided by these messages to get rid of them. If the statement, routine, or program is rarely used for example, because it is a test program, or a tool that is only used in exceptional cases Mark the statement with a pseudo comment. As you may know, the Code Inspector allows you to declare exceptions to its rules in the form of pseudo comments in the code. You will find the appropriate comment in the documentation on the checks. Using the pseudo comment you indicate that you have noticed the Code Inspector message, but think that it is not relevant in this case. But under no circumstances set pseudo comments automatically without first using your common sense If the statement is frequently executed Try to re write the WHERE clause so that an existing index can be used. Maybe you can read a missing index field cheaply from a buffered table to fill the gap in the WHERE clause. Or maybe you even have the required information at hand, but forgot to add it to the WHERE clause. Always formulate the WHERE clause as complete as possible. Do not read entries from the database just to throw them away shortly afterwards as it is sometimes done in SELECT ENDSELECT loops with a CHECK statement inside. Most check conditions on fields of the accessed database table can be incorporated into the WHERE clause. Side remark try to formulate the fields in the WHERE clause always in the same order preferably according to the primary key order. This will save space in the databases statement cache. If the statement is frequently executed and cannot be rewritten Adjust one of the existing indexes However, be careful, changes on an index can affect other statements Alternatively, create a new index Be careful here, too, as every additional index stresses the database. The reason for this is that every modification of the indexed database table that touches one or more of the index fieldsalso leads to a modification of the corresponding indexes. ABAP4 Tuning Checklist ABAP Development. ABAP4 Tuning Checklist. D Link Router Drivers. General Considerations 1. Is the program YEAR 2. Is the program US ready               taking into consideration the US volumes, will the program ever  finish  Can data be selected for just the US selection parameters at                                   company code level, plant, purch org, sales div, etc. SQL SELECT statements 3. Is the program using SELECT statements                         Convert them to SELECT column. Are CHECK statements for table fields embedded in a SELECT. ENDSELECT loop, or in a LOOP AT. ENDLOOP                         Incorporate the CHECK statements into WHERE clause of the SELECT  statement, or the LOOP AT statement. Do SELECTS on non key fields use an appropriate DB index or is table           buffered                         Create an index for the table in the data dictionary or buffer tables if they   are read only or read mostly. Is the program using nested SELECTs to retrieve data                                     Convert nested selects to database views, DB joins 4. SELECT xxx   FOR ALL ENTRIES IN ITAB Nested open select tatements are usually bad, and unless the amount of data is enormous, these types of reads should be avoided. They are usually used as they are the safest way to program you will never have any         memory issues. The technical explanation as to why this is bad follows    feel free to tune out for the remainder of the paragraph. Open selects read just one record from the database at a time. Each time you do this, the app server and database server need to communicate. The packet size of this     communication is typically much larger than the size of the record, so most of the packet is waisted with this type of read. When system load is heavy,  this can slow performance. Combine this with the overhead of accessing  the database many times vs just once. Are there SELECTs without WHERE condition against files that grow  constantly BKPFBSEG,MKPFMSEG,VBAKVBAP. Program design is wrong back to drawing board. Are SELECT accesses to master data files buffered no duplicate accesses      with the same key                         Buffer accesses to master data files by storing the data in an internal table  and filling the table with READ TABLE. BINARY SEARCH method. Is the program using SELECT. APPEND ITAB. ENDSELECT techniques   to fill internal tables                         Change processing to read the data immediately into an internal table   SELECT VBELN AUART. INTO TABLE IVBAK. Is the program using SELECT ORDER BY statements                         Data should be read into an internal table first and then sorted, unless there  is an appropriate index on the order by fields. Is the programming doing calculationssummations that can be done on the database via SUM, AVG, MIN or MAX functions of the SELECT statement                        use the calculation capabilities of the database via SELECT SUM. Do any SELECT statements contain fully qualified keys all keys are   specified to match a single value in the WHERE clause but not the keyword          SINGLE                         the syntax checker wont catch this neither does the database optimizer Are there any SELECT or LOOP AT structures that are only attempting to      retrieve the first match on a generic key lookup                        use the EXIT statement, or UP TO 1 ROWS option in the case of       SELECT, to break the loop avoid unnecessary iterations. Do UPDATEINSERTDELETE statements occur inside loops                        save all accumulated changes in an internal table and then use the SQL   array statements to perform the updates  INSERT lt tab FROM TABLE lt itab. Are only a few fields being changed by an UPDATE statement                         use the SET lt field lt value  clause of the UPDATE statement rather  than updating the entire record. Is the program insertingupdating or deleting data in dialog mode not via an    update function module                         Make sure that the program issues COMMIT WORK statements when one or more logical units of work LUWS have been processed. Internal Table Processing also see 4 1. Are internal tables processed using READ TABLE itab WITH KEY. BINARY SEARCH technique                         Change table accesses to use BINARY SEARCH method. Note that this is ONLY possible if the table is sorted by the access key  Add a SORT if  not already there. Is a semi sequential scan of a large, sorted internal table being performed by   a LOOP AT. WHERE, or LOOP AT. CHECK statements to        continueterminate the loop                         Use a READ TABLE with BINARY SEARCH to retrieve first record in  the sequence matching the lookup keys, then LOOP AT. FROM SY  TABIX 1  until one of the key field values changes this way you get only the records you need, rather than reading up to the starting point. Is a condensed, summary internal table being built by READ TABLE. BINARY SEARCH and then sum, INSERT or APPEND                        Use the COLLECT statement, and SORT the result after the table is                             complete. Is a COLLECT statement being used simply to avoid duplicate entries in an     all character fields no numeric fields types I, P or F internal table                         Use the READ TABLE. WITH KEY. BINARY SEARCH  with an                                    INSERT. INDEX SY TABIX  to build the table. Are internal tables being SORTed without any field qualifiers                         Specify the actual fields to be used  SORT lt tab BY lt fld. The fewer fields, the betterAre internal tables being copied or compared record by record                         Use the option to refer to the entire table in one line of code                           TAB2 TAB1                copy TAB1 to TAB2                          IF TAB1 EQ TAB2         compare TAB1 to TAB2. String Processing 2. Are strings being built upbroken down manually with code fragments                        use the new CONCATENATESPLIT statements, with the SEPARATED                                BY SPACE option of CONCATENATE if desired. Are the older, obsolete string handling functions for finding string length, or     centeringright justifying strings being called                         use the newer strlen , WRITE. TO. CENTERED,                                                           WRITE. TO. RIGHT JUSTIFIED  statements. Are field strings records being set to a special character, other than    SPACE, via a CLEAR and then a TRANSLATE instruction                         use the new CLEAR lt record WITH lt char statement  esp. SAP batch input generation programs to set the no value for this                                   field indicator, which has a default symbol of Miscellaneous 2. Is an internal table filled with a massive amount of data approx. FIELD GROUPS declaration, and the INSERT, EXTRACT, SORT and LOOP instructions to define, create, sort and process such large volumes of data more efficiently.