Inspecting User Table and Index Statistics
Introduction
The number of records accessed (read, update, create and delete) during a backend operation is one significant factor that a software developer can influence during development time. The number of records accessed very often provides more constant insight into the final system performance than looking at a stop watch. Developers can influence the number of records read for instance through database queries and the resulting index selection or by extensive aggregation of database records into Business Entity result sets, e.g. in calculated fields.
The total number of database records read during a Business Entity Fetch Data operation should stand in a reasonable relation to the resulting temp-table records made available to the consumer.
The SmartComponent Library supports developers with providing insight into the number of records and index nodes accessed during development and testing. Those utilities are based on the _userTableStat and _userIndexStat VST’s (virtual system tables).
See
http://knowledgebase.progress.com/articles/Article/19450
http://knowledgebase.progress.com/articles/Article/000048306
for details.
Inspecting User Table and Index Access in the Business Entity Tester
The Business Entity tester provides a simple way of inspecting the record and index access. From the Business Entity Toolbar, developers can open the “User Table & Index Statistics” view.
The contents of this view are always updated when the Business Entity Tester performs a request to the Business Entity.
Inspecting User Table and Index Access during and AppServer call
While testing an Application (with or without the AppServer), developers can get the same statistics written into the AppServer or Client Logfile. The output is always performed at the end of a service interface interaction.
[17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL ######################################################################################################## [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL Table Name Record Reads Updates Creates Deletes [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL -------------------------------------------------------------------------------------------------------- [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore 1 1 1 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartRelation 51 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartSecurityObject 1 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartSecurityObjectToken 1 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartSecurityToken 1 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartTable 103 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartValueList 1 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Customer 201.222 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Department 100 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Employee 100 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Salesrep 101 0 0 0 [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL -------------------------------------------------------------------------------------------------------- [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL Index Name Index Reads Creates Deletes [17/03/04@20:14:47.639+0100] P-129268 T-129352 1 AS-7 APPL -------------------------------------------------------------------------------------------------------- [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartAttachment.AttachmentRole 100 0 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartAttachment.CommentGUID 100 0 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore.ContextStoreGU 1 1 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore.Created 0 1 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore.DomainUser 0 1 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore.LastAccessed 0 1 0 [17/03/04@20:14:47.640+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartContextStore.SessionID 1 1 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartRelation.RelationGuid 53 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartSecurityToken.SecurityToken 1 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartTable.DatabaseTable 3 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB.SmartTable.TableGUID 100 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB._Field._Field-Position 26 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB._Field._File/Field 5 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB._Index-Field._Index/Number 26 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL SmartDB._Index._File/Index 16 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Customer.CountryPost 201.121 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL sports2000.Salesrep.SalesRep 200 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL sports2000._Field._Field-Position 30 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL sports2000._Index-Field._Index/Number 13 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL sports2000._Index._File/Index 8 0 0 [17/03/04@20:14:47.641+0100] P-129268 T-129352 1 AS-7 APPL ########################################################################################################
This output is loaded by Consultingwerk.OERA.TableStatistics.RequestUserTableStatsMonitor class. This class subscribes itself to the Service Interfaces Activated and Deactivated events. To activate this class, developers need load the service definition from the Consultingwerk/OERA/TableStatistics/services_request_monitor.xml file. For an AppServer this can be achieved by adding following to the parameter of the AppServer startup procedure:
sessionStartupProcParam= services= Consultingwerk/OERA/TableStatistics/services_request_monitor.xml