Performance Optimization for CA Plex 7.3 using SQL Server ODBC 17/18 Drivers

Products

• CA Plex

Issue/Introduction

Users upgrading to CA Plex 7.3 may experience severe performance issues when using SQL Server ODBC 17 or 18 drivers.

A common symptom is the `blockfetch` function failing to fetch multiple rows as expected, instead retrieving only one row at a time from the driver.

Environment

CA Plex 7.3
SQL Server
Microsoft ODBC Driver 17 or 18 for SQL Server
WinC/WinNTC variants

Cause

CA Plex 7.3 runtime queries the ODBC driver at the start of communication to determine the communication mode.

Differences in how the ODBC 17/18 drivers report capabilities compared to older versions, combined with network latency or environment configuration, can lead to sub-optimal fetching and DLL loading behavior.

Resolution

To resolve performance bottlenecks, apply the following adjustments:

1. Enable DLL Caching

Configure the application to cache loaded DLLs to prevent frequent unloading/reloading, which significantly impacts performance in CA Plex 7.3

Within the Application.ini`file, make the following change within the [Settings] section:

Add or update the following property:

[Settings]
Cache DLL References=True

Note:  Ensure this is applied to all relevant INI files if multiple files exist

2. Optimize Artifact Deployment

High network latency can exacerbate performance issues when application binaries are accessed over a network. 

Transition from running built artifacts on shared/mapped network drives to local deployment on the client machine or application server. 

3. Review Debug Logging

Ensure that conditional debug logging or diagnostic messages are completely disabled in the production build. Even inactive debug calls can introduce overhead through string construction and casting operations within loops.

Additional Information

If the database is remote, ensure network latency is within acceptable limits, as environmental factors often play a larger role in performance lags than the core CA Plex runtime logic.