Analyzing Database Access with SQL Trace

Copy the template class. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

1. Copy class /LRN/CL_S4D401_ATS_PROFILING to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).

   1. In the Project Explorer view, right-click class /LRN/CL_S4D401_ATS_PROFILING to open the context menu.

   2. From the context menu, choose Duplicate ....

   3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SOLUTION, where ## stands for your group number.

   4. Adjust the description and choose Next.

   5. Confirm the transport request and choose Finish.

2. Activate the copy.

   1. Press Ctrl + F3 to activate the class.

To further analyze the performance issues, you activate the SQL Trace and execute your ABAP class ZCL_##_SOLUTION as a console app.

1. If it is not open, open your ABAP class ZCL_##_SOLUTION in the editor.

   1. Press Ctrl + Shift + A and enter the name of your ABAP class.

   2. Choose OK.

2. Open the SQL Trace State window for your ABAP cloud project.

   1. In the Project Explorer view on the left, right-click your cloud project to open the context menu.

   2. Choose SQL Trace ....

3. Check the current SQL Trace State. If the trace is switched on, close the dialog window and come back in a few minutes.

   1. Choose OK to close the dialog window.

4. If the trace is currently switched off, switch it on. Then close the dialog window.

   1. Choose Activate to switch on the SQL trace.

   2. Choose OK to close the dialog window.

5. Execute your global class ZCL_##_SOLUTION as console app.

   1. Press F9 to execute the ABAP class as console app.

6. As soon as the application has finished, switch off the SQL trace and open the trace directory.

   1. As soon as the green progress indicator in the bottom right corner disappears, right-click your cloud project in the Project Explorer and choose SQL Trace ....

   2. Choose Deactivate to swithc off the trace.

   3. Choose View Trace Directory to display a list of your SQL traces.

Analyze the SQL Trace result. Filter for database accesses that originate from your ABAP class and find out which database tables have been accessed for how often.

1. On the Technical Monitor Cockpit, display the trace records of the newest trace.

   Hint

   By default, the list of traces is sorted descending by column Time Stamp.

   1. Choose the topmost list entry.

   2. Choose Trace Records.

2. Analyze the list of trace records.

   1. You find the displayed number of rows above the header of column Time Stamp.

   2. You find the maximum number of rows that are displayed next to the Go button.

3. Filter the list of trace records. Only display records that originate from your ABAP class ZCL_##_SOLUTION.

   Hint

   We recommend that you use the selection filter left from the Go button and not the display filter that you can set by choosing the header of column Program.

   1. Choose Filter (...) from the toolbar on the left of the Go button.

   2. Choose Program from the drop down list at the top of the dialog window.

   3. Make sure that the Like option is selected.

   4. Replace the input prompt Enter value with the name of your ABAP class (Z00_SOLUTION).

   5. Choose OK.

   6. Choose Go to refresh the selection.

4. Sort to the list of trace records. Find the SQL statements that accessed the most records.

   Note

   By default, the list of trace records is sorted by the Time Stamp column. If you want to sort it using the Duration column, you have to remove the existing sorting first.

   1. Choose the three horizontal dots on the right of the Go button.

   2. Choose Reset Sort.

   3. Choose Sort(0) on the left of the Go button.

   4. Find the Accessed Records [#] column. Tick the box on the left and choose the button with the downward arrow.

   5. Choose OK to close the dialog window.

   6. Choose Go.

5. Remove the sorting by the Accessed Records column and sort the list descending by the Duration column.

   1. Choose the three horizontal dots on the right of the Go button.

   2. Choose Reset Sort.

   3. Choose Sort(0) on the left of the Go button.

   4. Find the Duration [µs] column. Tick the box on the left and choose the button with the downward arrow.

   5. Choose OK to close the dialog window.

   6. Choose Go.

6. For the most expensive single record statement, navigate to the SQL statement display.

   1. Place the cursor on the row with value 1 in the Accessed Records column and the largest value in the Duration column.

   2. Choose SQL Statement from the navigation pane on the top.

   3. You find the table name in the FROM clause.

7. Open the SELECT statement in the ABAP development tools.

   1. Choose Show ABAP Source in ADT.

To further improve the performance of your code, eliminate the repeated SELECT SINGLE access to database table /LRN/CONNECTION. You know that table /LRN/CONNECTION contains a relatively small number of records. Therefore, you decide to buffer the entire content of the database table in a static attribute (suggested name: connections_buffer). You fill the buffer in the static constructor (method class_constructor) and replace the SELECT SINGLE statement in the instance constructor with a read access to the buffer.

1. Navigate to the definition of attribute connection_details and from there to the definition of structure type st_connection_details.

   1. In the FROM clause of the SINGLE SELECT statement, place the cursor on connection_details and press F3. Alternatively, you can hold down the Ctrl key and choose connection_details.

   2. In the DATA statement, place the cursor on st_connection_details and press F3. Alternatively, you can hold down the Ctrl key and choose st_connection_details.

2. Copy public structure type st_connection_details to a new private structure type (suggested name: st_connections_buffer).

   1. Select the entire TYPES: statement (including row END OF st_conection_details.) and press Ctrl + C.

   2. Scroll down to the private section, place the cursor above the ENDCLASS statement and press Ctrl + V to insert the copied code.

   3. In the inserted code, replace st_connection_details with st_connections_buffer.

3. At the beginning of the component list of st_connections_buffer, add the key fields of database table /LRN/CONNECTION.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      123456789
      
      TYPES:
            BEGIN OF st_connections_buffer,
              airport_from_id TYPE /dmo/airport_from_id,
              airport_to_id   TYPE /dmo/airport_to_id,
              departure_time  TYPE /dmo/flight_departure_time,
              arrival_time    TYPE /dmo/flight_departure_time,
            END OF st_connections_buffer.
      

4. Declare a private static attribute connections_buffer as an internal table with st_connections_buffer as row type.

   1. At the end of the class definition, add the following code:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      123
      
        CLASS-DATA connections_buffer TYPE TABLE OF st_connections_buffer.
      

5. Use a quick fix to generate a static constructor (class constructor) for class lcl_passenger_flight.

   1. Scroll up to statement CLASS lcl_passenger_flight DEFINITION..

   2. Place the cursor on lcl_passenger_flight and press Ctrl + 1 to display a list of available quick fixes.

   3. From the list, choose Generate class constructor.

6. Edit the implementation of method class_constructor. Implement a SELECT statement that reads all records from database table /LRN/CONNECTION into the connections_buffer attribute.

   1. Adjust the code as follows:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      12345
      
        METHOD class_constructor.
      
        ENDMETHOD.
      

7. In the implementation of method constructor, replace the SELECT SINGLE statement with a read access to the connections_buffer.

   Note

   Keep in mind that attribute connection_details has fewer components than the row type of the connections_buffer attribute.

   1. Select the entire SELECT SINGLE statement and choose Ctrl + < to add a comment sign in front of each row.

   2. After the commented SELECT SINGLE statement, add the following code:

      Code Snippet

      Copy code

      Copy code

      Switch to dark mode

      Switch to dark mode

      12345678
      
      *      SELECT SINGLE
      *        FROM /lrn/connection
      *      FIELDS airport_from_id, airport_to_id, departure_time, arrival_time
      *       WHERE carrier_id    = @carrier_id
      *         AND connection_id = @connection_id
      *        INTO @connection_details .
      

Activate and test your global class. Execute the class as console app. Repeat the profiling to measure the performance improvement.

1. Activate your global class and execute it as console app.

   1. Press Ctrl + F3 to activate the class.

   2. Press F9 to execute the class as console app.

2. Start the runtime measurement with the ABAP Profiler.

   1. Right-click in the editor and choose Profile As → ABAP Application (Console).

   2. In the Trace Parameters dialog box, choose Finish without changing any of the settings.

3. Switch to the Profiler perspective and analyze the trace overview.

   1. Choose Window → Perspective → Open Perspective → ABAP Profiling.

   2. In the ABAP traces view, choose Refresh. Alternatively, you can place the cursor anywhere in the ABAP Traces view and press F5 to re-load the list of trace results.

   3. Double-click the most recent trace (this is the topmost entry under your project).

   4. The overall runtime and the distribution between ABAP and Database are displayed in the lower section of the Overview.