Lesson 2: Running an updated procedure against the baseline procedure

Update a procedure and run it against the baseline procedure. Collect diagnostic tracing data about the two procedures for procedure profiling.

Prerequisites

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Baselining with procedure profiling.

This lesson assumes that you have completed all preceding lessons. See Lesson 1: Creating a baseline procedure.

Task

Start Sybase Central. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
In Sybase Central, connect to app_profiling.db as follows:
1. Click Connections » Connect With SQL Anywhere 16.
2. In the Connect window, complete the following fields to connect to the test database, app_profiling.db:
  1. In the Authentication field, type Database.
  2. In the User ID field, type DBA.
  3. In the Password field, type sql.
  4. In the Action dropdown list, select Start and connect to a database on this computer.
  5. In the Database file field, type C:\AppProfilingTutorial\app_profiling.db.
  6. In the Start Line field, type dbsrv16 -x tcpip.
3. Click Connect.
In Sybase Central, click Mode » Application Profiling.

If the Application Profiling Wizard does not appear, click Application Profiling » Open Application Profiling Wizard.
Start the Application Profiling Wizard:
1. On the Welcome page, click Next.
2. On the Profiling Options page, click Stored procedure, function, trigger, or event execution time.
3. 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 click Execute from Interactive SQL. Procedure profiling is enabled, so execution details for the procedure are captured.
Close Interactive SQL.
View the profiling result:
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, click View » Refresh Folder.
  
  The execution times appear for each line in the baseline procedure.
Save the profiling results:
1. Right-click the database and click Properties.
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. For example, C:\AppProfilingTutorial\baseline.
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, click Mode » Design.
In the left pane, browse to and select the baseline procedure in Procedures & Functions.

On the SQL tab in the right pane, replace the existing INSERT statement with the following INSERT statement:

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

Click File » Save.

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

Results

You have updated the procedure and run it against the baseline procedure, collecting data about the two procedures in a tracing session.

Next

Proceed to Lesson 3: Comparing the procedure profiling results.

Discuss this page in DocCommentXchange.