Tutorial to Improve Code Performance with Visual Expert

Why use this tutorial?

This tutorial helps review and improve the response time of database code (procedures, functions, triggers, queries…).

It is meant for projects using:

  1. SQL Server databases with Transact-SQL code
  2. Oracle databases with PL/SQL code

Step 1: Configure your Visual Expert Project

VE must access the database to collect performance data: either your Visual Expert instance can directly connect to the database, or an authorized personnel (DBA for instance) will run a Visual Expert utility against the database to collect it.

Please follow the procedure corresponding to your configuration:

  1. Oracle - VE can connect directly to the Database
  2. Oracle - VE CANNOT connect directly to the Database
  3. SQL Server - VE can connect directly to the Database
  4. SQL Server - VE CANNOT connect directly to the Database

Step 2: Review your code

Once the performance data is accessible, VE must analyze your code again, to combine performance data and source code.

Then, you will be able to answer the following questions: 

Report type Purpose
Identify slow objects Find and sort objects by low response time.
Optimize a slow object Break down the response time of a slow object into smaller pieces to identify and remove bottlenecks.
Optimize slow SQLs List the SQL queries accessing a table. Sort them by response time. Review their execution plan to improve them.
Optimize a business process Analyze the chain of calls behind an application feature: identify slow objects affecting the user experience.
Review production metrics Identify the largest tables, the most frequently executed objects, the objects frequently referenced by code, etc.