Spotfire Information Designer: Measuring database connection and data retrieval time

Description:

This article describes a method that can be used to measure the time that it takes to connect to a database and retrieve metadata when expanding a data source object in Spotfire Information Designer.

Solution:

1. Enable detailed logging:

    - Set Spotfire Server logging to "TRACE" mode in the server configuration.

2. Locate and monitor log file \tomcat\logs\informationservices\server.log on Spotfire Server machine.

3. Identify relevant log entries related to database metadata operations, such as:

     DatabaseMetadata.getCatalogs()

     DatabaseMetadata.getTables()

     DatabaseMetadata.getColumns()

     Example log pattern:

TRACE [timestamp] [username, #null, #threadID] util.sql.PoolingDataSource: Acquired idle connection from data source
DEBUG [timestamp] [username, #null, #threadID] ds.sql.BasicJDBCMetadataProvider: Calling DatabaseMetadata.[operation]
...
DEBUG [timestamp] [username, #null, #threadID] ds.sql.BasicJDBCMetadataProvider: Retrieved metadata items: [item list]

4. Calculate Time:

    - Record the timestamp of the first relevant log entry (Calling DatabaseMetadata.[operation]).
    - Record the timestamp of the last relevant log entry (metadata retrieval completion).
    - Calculate the difference between these timestamps to estimate the total operation time.

Considerations:

    - Log entries may be a combination of TRACE and DEBUG level messages.
    - Exact class names or operations might vary between Spotfire versions.
    - The calculated measurements are approximate, since there are no official measurements available.

Note: Enabling TRACE logging may impact server performance. Use this method judiciously and preferably in a non-production environment. Always revert logging levels to normal after testing.

Limitations:

   - This method relies on log analysis and may not provide exact millisecond-level precision.
   - Log patterns and available information may change in future Spotfire versions.