Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Baselining using a profiling log file

In Sybase Central, you can save procedure profiling results to file to use as baseline information. For example, if you are making incremental changes to a procedure to see if it runs faster, you can run it after each change you make, and compare the results to the results that were saved to file.

In order to understand how to use baselining with procedure profiling in Sybase Central, you must first be familiar with the results provided for procedure profiling. See Analyzing procedure profiling results.

To perform baselining using a profiling log file

1. Enable procedure profiling on the database you wish to profile.

   1. Connect to your database as the DBA.

   2. Choose Mode > Application Profiling.

      • If the Application Profiling wizard appears, follow the wizard instructions.

      • If the wizard does not appear, choose Application Profiling > Open Application Profiling wizard, and then follow the wizard instructions.

   3. On the Profiling Options page of the Application Profiling wizard, select Stored Procedure, Function, Trigger or Event Execution Time. Do not select any of the other options.

   4. Select Finish.

      The database server begins procedure profiling. If you attempt to switch to another mode, the database server asks whether you want to continue procedure profiling. Select No to continue working in other modes while profiling continues.

2. Right-click the procedure in the Procedures & Functions folder and choose Execute From Interactive SQL.

   The database server executes the procedure from Interactive SQL. Since procedure profiling is enabled, execution details for the procedure are captured.

3. Close Interactive SQL.

4. Save the profiling results.

   1. Right-click the database and choose Properties.

      The Database property sheet for the database 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 choose a location and file name for the profiling log file.

   4. Click Apply.

      The procedure profiling information gathered from the time procedure profiling was enabled is saved to the specified profiling log file (.plg).

5. Enable baselining against the profiling log file.

   1. On the Profiles Settings tab of the Database property sheet, 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 Database property sheet.

6. Make the desired changes to the procedure.

   1. In the left pane, browse to, and select, the procedure in the Procedures & Functions folder.

   2. On the SQL tab in the right pane, make the desired changes to the SQL code for the procedure.

   3. Choose File > Save.

7. Right-click the procedure and choose Execute From Interactive SQL.

   The database server executes the procedure from Interactive SQL.

8. Close Interactive SQL.

9. In the right pane in Sybase Central, click the Profiling Results tab to view execution details.

   Notice that there are two new columns: Execs. +/-, and ms. +/-. These columns result from comparing statistics in the profiling log file to the statistics captured during the most recent execution of 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 have improved 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. For example, -3 indicates that the line of code in the procedure ran 3 milliseconds faster than the baseline.