Database Optimization Analysis

Overview

Occasionally, the SQL engine cannot fully optimize a query because it does not have enough information about table content to determine the best index. The "Analyze" feature performs a one-time analysis of all selected database tables, allowing the optimizer to more accurately determine how each table can be optimized when used in a query.

The time to perform the analysis is dependent on the number of tables, the number of records per table, and the number of indexes per table. For each table in the database, the process must read all records for each defined index. This process can take seconds or minutes per table, or larger tables might take hours.

Although the analysis is intended to be run only once or very rarely, it can be run at a later time if queries do not appear to be optimized as expected. The analysis of the entire database can be updated at any time, or just the tables that need to be updated.

em-analyzedb.png

The image above shows the information that is displayed when you select a database from the Databases list and choose the Tables tab. The display shows a list of each table in the database in alphabetical order, the last time that an analysis was performed on the table, the number of records in the table, and the type of file.

Please note that the "Last Analysis" information will not be available if the analysis was run on a version of BBjServices prior to 2.03. If the information is not available, a "?" will be displayed. There will also be a "?" on tables that could not be analyzed for some reason (in the example above, the data files were not present).

The analysis process is performed in the background – that is, the Enterprise Manager can still be used while analysis is going on. In addition, at any time, the analysis can be stopped by clicking the stop analysis button. Please be aware, however, that the analysis process will stop after the current table analysis is complete.

The refresh button allows the user to refresh the currently displayed information.

To start analysis, follow the steps listed below:

  1. Use the mouse to select the tables you want analyzed. Holding down the CTRL key while clicking will keep your current selection(s) selected while adding the new one. You can also press CTRL-A to select all tables.

  2. Click the [Start Analysis] button.

  3. In BBj 5.0 and higher, SQL - Analyze Database and SQL - Analyze Table are available.

See Also

Automatic Database Analysis



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.