SP/Triggers/Views Analyzer

The Stored Procedure/Trigger/Views Analyzer can be found in the IBExpert Tools menu. (This feature is unfortunately not included in the free IBExpert Personal Edition.)

It allows the user to view and analyze how the database performs individual operations/statements in a stored procedure, trigger or view. For example, certain indices perhaps may not be used by the database server as the statistics are too high; this can be solved simply by using the IBExpert Database menu item Recompute selectivity of all indices to update the selectivity. Or when backing up an older InterBase® version and restoring to a new Firebird/InterBase® version, the procedures and triggers appear not to work as it is often necessary to first Recompile all stored procedures and triggers (also found in the IBExpert Database menu).

The database to be analyzed can be selected from the drop-down list of all connected databases (the first toolbar item). By clicking on the Start Analyzing icon, it loads all stored procedures and triggers for the active database.

They are all automatically analyzed, i.e. each procedure/trigger is split up into its individual statements (the first SQL row is displayed in the Statement column; the full code is displayed in the lower Statement window). All statements with any sort of problems (no index, compiler warning etc.) are highlighted, and need looking at more closely.

The indices used for each operation are displayed in the right-hand Expected Plan column; details are displayed in a tree form in the lower Expected Plan window. Possible compatibility problems are indicated in the Compatibility column with details in the Compatibility of Types window below:

The last column displays compiler warnings, again with details in the lower window (see illustration below).

The user can specify exactly what he would like to analyze by deactivating or activating the toolbar icons (SP/Triggers/Views Analyzer toolbar):

The analysis results can be filtered by the criteria listed in the drop-down Filter by list:

• SP/Trigger name
• Operation
• Table View
• Statement
• Expected Plan
• Compatibility
• Compiler warnings

and supplemented by the user-specified filter string to the right, to search for specific objects, operations or problems. This filter can even be inverted (check box option on the right).

As with all IBExpert grids the contents can be sorted by clicking on the desired column header (e.g. sort according to Name, Table/View, statement etc.). By clicking on the left-hand column header (the unnamed column to the left of the SP/Trigger column), the red highlighted objects (i.e. those with any sorts of problem that need looking at more closely) are grouped together.

The Procedure, Trigger, Table or View editors can be quickly started by double-clicking on a selected field, allowing the user for example, to quickly and easily insert an index.

Column headers can also be dragged to the gray area below the toolbar, to group by the column selected:

The above illustration displays all stored procedures and triggers grouped by the procedure or trigger name. By clicking '+' or '-', or double-clicking on the list name, the individual operations can be easily blended in or out.

It is also possible to group by more than one criteria:

The lower window displays the SQL text for a selected operation on the Statement page. The statements can easily be copied and inserted into a text editor or the IBExpert SQL Editor, using the context-sensitive right-click menu (please refer to the SQL Editor Menu for further details).

In case it is of interest, the SP/Triggers/Views Analyzer was realized using the Developer Express component.