Review production metrics and statistics

Visual Expert collects and presents certain information about the code and database in production, which can help optimize performance.

Visual Expert can answer the following questions:

Largest tables (by Row Count)

This feature is available when you select the root of the Tables in the treeview.
It sorts tables by number of records, as shown below.

Table Row Count Macro in Visual Expert

The result displays the number of records besides each table - the largest tables are listed first.

You can limit the number of tables by using the following parameter:

Table Row Count result displaying number of rows

The above configuration will list the 10 tables with the largest number of records (see result below).

Table Row Count result displaying top top 10 tables having the most number of records in the database

Largest tables (by volume of data)

This feature is available when you select the root of the Tables in the treeview. It sorts tables by volume of data.
The result shows the table size in MBs beside each table, as shown below.

identifying tables which occupy large amounts of memory

You can adjust the number of tables displayed with the following parameter:

Number of tables to be displayed

The above configuration will list the 10 largest tables of the database (see result below).

top 10 largest tables in size in the database

Most Frequently Executed objects

Visual Expert can identify the most frequently executed objects in the database.

Should you engage in an optimization process, you may want to prioritize these objects, since they are executed more frequently than others.

This feature is available in the treeview, after selecting the root of procedures, functions and triggers:

identify the objects which are most frequently executed in the database

You can adjust the number objects displayed with the following parameter:

declare the number of objects to be displayed

The example above lists the five most frequently executed procedures (see result below).

top five most frequently executed procedures displayed

Most Referenced Objects

Visual Expert can identify the objects that are most frequently called/referenced by other objects.

This feature is available in the treeview, after selecting the root of tables, functions, procedures, triggers and synonyms:

identify the objects which have higher impacts in the project

You can check out the complete list of references for a given object: simply select this object in the result and click on “Impact Analysis” in the navigation bar.

check the impact analysis

 

In the above example, the table “ProductCategory” is referenced by 3 other objects

You can adjust the number of objects listed by using the following parameter:

configure the number of objects to be displayed

The above configuration would display the 5 most referenced functions (see result below).

top 5 functions having highest impacts in the project displayed