Tutorial: Baselining with procedure profiling

Use this tutorial to learn how to use the Application Profiling Wizard to create a baseline that you can use for comparison purposes when improving performance.

Introduction

Procedure profiling provides execution time measurements for procedures, user-defined functions, events, system triggers, and triggers. You can set your saved results as a baseline and make incremental changes to the procedure and run it after each change you make. This allows you to compare the new results to the baseline.

Note

This tutorial assumes you have followed the setup steps for the application profiling tutorials. If you have not, see Setting up for the application profiling tutorials.

Preparation

Tip

You can copy and paste the SQL statements in this tutorial into Interactive SQL.

To create a baseline procedure

Start Sybase Central and connect to the test database app_profiling - DBA with the user ID DBA and the password sql.

If you have not created the test database, see Setting up for the application profiling tutorials.

If you are unfamiliar with starting Sybase Central and connecting to a database, see Connecting to a database on your own computer from Sybase Central or Interactive SQL.
In the left pane, select the app_profiling - DBA database, and then choose File » Open Interactive SQL.

Interactive SQL starts and connects to the app_profiling - DBA database.

In Interactive SQL, run the following SQL statements:

Create a table:

CREATE TABLE table1 (
Count INT );

Create a baseline procedure:

CREATE PROCEDURE baseline( )
   BEGIN
      INSERT table1 
         SELECT COUNT (*)
            FROM rowgenerator r1, rowgenerator r2, 
                 rowgenerator r3 
         WHERE r3.row_num < 5;
   END;

Commit the changes you made to the database:
COMMIT;

Close Interactive SQL.

Profiling the problem

To run an updated procedure against the baseline procedure

In Sybase Central, choose Mode » Application Profiling.

If the Application Profiling Wizard does not appear, choose Application Profiling » Open Application Profiling Wizard.
On the Welcome page, click Next.
On the Profiling Options page, select Stored Procedure, Function, Trigger, Or Event Execution Time.
Click Finish.

The database server begins procedure profiling.
In the left pane of Sybase Central, double-click Procedures & Functions.
Right-click the baseline procedure and choose Execute From Interactive SQL. Procedure profiling is enabled, so execution details for the procedure are captured.
Close Interactive SQL.
View the profiling results.
1. In the left pane of Sybase Central, select the baseline procedure.
2. Click the Profiling Results tab in the right pane. If no results appear, choose View » Refresh Folder.
  The execution times appear for each line in the baseline procedure.
Save the profiling results.
1. Right-click the database and choose Properties.
  The App_Profiling - DBA Database Properties window appears.
2. Click the Profiling Settings tab.
3. Select Save The Profiling Information Currently In The Database To The Following Profiling Log File, and then specify a location and file name for the profiling log file.
4. Click Apply. Do not close the properties window.
  The procedure profiling information that was just gathered is saved to the specified profiling log file (.plg).
Enable baselining against the profiling log file.
1. On the Profiling Settings tab of the App_Profiling - DBA Database Properties window, select Use The Profiling Information In The Following Profiling Log File As A Baseline For Comparison.
2. Browse to and select the profiling log file you created.
3. Click Apply.
4. Click OK to close the App_Profiling - DBA Database Properties window.

Make changes to the baseline procedure.

In Sybase Central, choose Mode » Design.
In the left pane, browse to and select the baseline procedure in the Procedures & Functions.
On the SQL tab in the right pane, delete the existing INSERT statement.

Copy and paste the following SQL statement into the procedure:

INSERT table1 
   SELECT COUNT ( * ) FROM rowgenerator r1, rowgenerator r2, rowgenerator r3 
   WHERE r3.row_num < 250;

Choose File » Save.

In Procedures & Functions, right-click the baseline procedure and choose Execute From Interactive SQL.
Exit Interactive SQL when the procedure completes.

Analyzing the data

To compare the procedure profiling results

In Sybase Central, choose Mode » Application Profiling.

If the Application Profiling Wizard appears, click Cancel.
In the left pane of Sybase Central, in Procedures & Functions, click the baseline procedure.
In the right pane, click the Profiling Results tab.
Choose View » Refresh Folder.

Two new columns, Execs. +/- and ms. +/-, appear.

For each line in the procedure, the profiling results show the number of executions, the number of executions relative to the baseline procedure, the duration of execution, the duration of execution relative to the baseline procedure, and the percentage of total time the line took to execute.

The Execs. +/- and ms. +/- columns result from comparing statistics in the profiling log file to the statistics captured during the most recent execution of the procedure. Specifically, they compare number of executions and duration of execution, respectively, for each line of code in the procedure.

Typically, you are interested in the ms. +/- column, which indicates whether you improved the execution time for lines of code in the procedure. Faster times are indicated by a minus sign and red font. Slower times are indicated by no sign, and green font. In this tutorial, the value in the ms. +/- column should be a + sign along with a execution time in green font. The INSERT statement in the updated procedure has a slower time than the INSERT statement in the baseline procedure.