In the Collect statistics mode the debugger collects some statistical information (prepare/execute time, rows affected, indexed/non-indexed reads) for each execution of SELECT/INSERT/UPDATE/DELETE/MERGE/EXECUTE statements and calculates total and average values.

Example part 1:

To demonstrate the IBExpert Debugger's Collect Statistics mode we have used the IBExpert Demo Database, DB1, and the procedures, INITALL and DELETEALL with a simple step-by-step illustration. If you would like to follow this example yourself, please first install the IBExpert Demo Database, as described here.

1. Open the DB1 Demo DB.

2. Open the INITALL procedure.

3. Click on the toolbar Debugger icon (or start using [F8]).

4. Set the Debug mode (using the drop-down list in the Debugger toolbar) to Collect Statistics.

5. Specify the parameter CNT to 1,000.

6. Use [F9] oder click the Run button to start. (This may take a few minutes.)

7. Commit. You can view the statistics on the Debugger's Statistics page:

Example part 2:

Before proceeding further with the second part of this example, you will need to run the INITALL(1000) once without the Debugger, as the Debugger does not actually write any data, before proceeding with the DB1 DELETEALL procedure as follows:

1. Open the INITALL procedure.

2. Use [F9] oder click the Run button to start.

3. Specify the parameter CNT to 1,000.

4. Commit.

5. Open the DELETEALL procedure.

6. Click on the toolbar Debugger icon (or start using [F8]).

7. Set the Debug mode (using the drop-down list in the Debugger toolbar) to Collect Statistics.

8. Use [F9] oder click the Run button to start.

9. Go to the Debugger Statistics page:

10. For each row in the procedure you can now view the plan and time taken, along with further statistical data (indexed reads, non-indexed reads, etc.).

11. You can now work through the procedure, optimizing those parts that are taking the most time.

So, now you can get started on the optimization of your own procedures!

back to top of page
<< Stored procedure | IBExpert | Differences between recreate procedure and create or alter procedure >>