Access Gen should give a very quick response. If the response is generally slow for all functions then it may mean there is an issue with the network or the encyclopedia database.
Network Performance
Access Gen is reliant on the network over which it runs. If there are network performance issues that have an unacceptable impact on the performance of Access Gen then these must be addressed. The PING utility can be used to verify the response time of the network.
Tip: Dependent on the type of objects selected, Access Gen can use a combination of the Model Structure Cache and direct encyclopedia access to satisfy any given query. It can therefore be substantially quicker to ensure that only the object types of interest are selected for a query.
The output of expansion commands can also be made more concise and faster by changing the Object Filter settings under the Client View drop down menu. This will prevent excessive and unnecessary levels of data being passed between the Access Gen client and server.
Encyclopedia Performance
It is very important that statistics are run regularly against the encyclopedia to ensure that the response time of Access Gen functions is as fast as possible. A data extract can be increased by over a 100 times if statistics are not collected. Statistics should be collected on the DOBJ, DASC, DNAME, DTXT and DMDL tables for both Oracle and SQLServer encyclopedias.
The Oracle ANALYZE Command
The ANALYZE command analyzes the storage characteristics of tables, indexes, and clusters to gather statistics which are then stored in the data dictionary. The optimizer uses these statistics in a cost-based approach to determine the most efficient execution plan for SQL statements issued.
Note that statistics can be either computed or estimated, depending on the amount of overhead you are willing to allow for this purpose. Estimating statistics is quicker and needs fewer resources, although the process may still take some time.
Access Gen will give much improved performance and there may be benefits to encyclopedia usage in general if statistics are run regularly. For the purpose of Access Gen, performance will be enhanced if statistics are regularly run against the DOBJ, DASC, DNAME, DMDL, and DTXT tables.
The ANALYZE TABLE command can be run through SQL*Plus and gathers statistics at the table level (for example, the number of rows and blocks) and for all dependent objects, such as columns and indexes.
ANALYZE TABLE ESTIMATE STATISTICS;
e.g. ANALYZE TABLE DOBJ ESTIMATE STATISTICS;
If performance is still a problem and statistics are being run in your ORACLE environment on a regular basis, then Access Gen can be configured to use a different ORACLE Optimiser setting. The two options available are COST Based Optimisation and RULE Based Optimisation. As an installation default, Access Gen uses COST Based Optimisation which relies on the collection of statistics to ensure good performance. This can be switched to RULE Based Optimisation (which does not include the need to collect statistics) by running the SRVSETUP.EXE from a command prompt in the Access Gen Server directory and selecting ‘Y’ in answer to the question ‘Do you want to switch off database Optimisations?’. This will not affect the way that Gen operates in any way.
The SQL Server UPDATE STATISTICS Command As with Oracle, Microsoft SQL Server 2000 also has the UPDATE STATISTICS command that provides the query optimiser with the latest information on the distribution of the data values in the tables. This ensures that an efficient execution plan for SQL statements is issued. The options to perform a FULLSCAN or to SAMPLE the data values may be used depending on the overhead you are willing to allow for this purpose.
SQL Server 2000 does update the statistics automatically but the option to force the statistics to be updated can be used when necessary (to switch off the automatic updating of statistics use the NORECOMPUTE option). If the index details are omitted from the command all the indexes on that table will be updated.
Syntax:
UPDATE STATISTICS table
[ index | (statistics_name[,…n]) ]
[ WITH [
[FULLSCAN] |
SAMPLE number {PERCENT | ROWS}] ] [
[,] [ALL | COLUMNS | INDEX] [
[,] NORECOMPUTE]
e.g. UPDATE STATISTICS DOBJ (DOBJI1) WITH SAMPLE 50 PERCENT
If the index details are omitted from the command all the indexes on that table will be updated. Please consult your SQL Server manual for further information.
* The syntax for SQL Server 6.5 isdifferent to that shown here.