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.
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:
-
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.
-
Click the [Start Analysis] button.
-
In BBj 5.0 and higher, SQL - Analyze Database and SQL - Analyze Table are available.