ETL Performance Modernization for a Legacy Replication Application

After infrastructure changes caused a critical ETL process to slow from 15 minutes to nearly 50 minutes, Connective was engaged to diagnose and resolve the performance issues. Through targeted modernization, data-transfer optimization, and SQL Server tuning, we reduced runtimes to 105–120 seconds, delivering a 20× performance improvement and cutting server memory usage by 40–50%. The result is a significantly faster, more stable, and easier-to-maintain replication application.

A Midwest-based Healthcare manufacturer relied on a legacy scheduled ETL application to replicate data from a third-party ODBC source into SQL Server every hour. After significant infrastructure changes—including hardware upgrades, a Hyper-V migration, and backup schedule adjustments—the application’s runtime ballooned from 15 minutes to 40–50 minutes, causing operational delays and risk.

Connective was brought in to diagnose the root cause and stabilize the environment.

Our assessment uncovered several critical issues:

• The application loaded large database tables fully into memory without proper disposal.

• SQL Server was configured to consume nearly all available RAM, starving both the OS and the ETL process.

• Minimal logging made performance bottlenecks invisible.

• Inefficient DataAdapter/DataSet patterns created heavy memory pressure, exacerbated by recent infrastructure changes.

The combination resulted in extensive paging to disk and dramatic slowdowns.

Connective modernized the application and optimized the surrounding environment through targeted engineering improvements:

• Introduced dependency injection and clearer separation of concerns.

• Migrated configuration to appsettings.json with validation.

• Upgraded the application to .NET 8.

• Replaced manual file logging with structured logging (NLog).

• Added detailed metrics, row counts, and performance tracing.

• Replaced legacy DataSet operations with SqlBulkCopy, increasing speed and reducing memory load.

• Implemented batch TRUNCATE operations and streaming to minimize memory usage.

• Ensured proper disposal through using patterns.

• Improved exception safety, connection pooling, and GC behavior.

• Added robust, contextual error logging.

• Recommended adjusting SQL Server’s memory cap to free RAM for the OS and application.

The impact was immediate and dramatic:

• Runtime reduced from 40–50 minutes to 105–120 seconds

• 20× faster than post-degradation performance

• 8× faster than the original baseline

• 1900–2000% performance improvement

• Server memory consumption reduced by 40–50%

• Application stability, observability, and maintainability significantly improved

What began as a performance recovery engagement resulted in a modernized, future-ready ETL application with vastly improved reliability and operating efficiency.