Cutting a Law Firm's Nightly ETL from 6+ Hours to 1 Hour 45 Minutes

SQL Server · T-SQL · SQL Agent · SQL Server Analysis Services (SSAS)

The Problem

A large international law firm's nightly ETL job (which loads about 10 years of firm data and builds multiple SSAS analysis cubes) had increased to more than six hours. Because the firm operates across the US, Europe, and Asia, there is no real "overnight" to run the job during. A job that long meant some offices started their day on stale data or slow dashboards/reports. If the job failed, there was no time to rerun it. Rather than adding more hardware, the goal was to fix underlying inefficiencies.

What I Found

The slowdown came from patterns repeated across the pipeline rather than from one bad query:

• Several procedures joined many large tables and views in a single statement, which produced inefficient execution plans when working with a decade of data.
• Staging logic read from layered views, so expensive joins were recalculated every time a view was referenced, and the optimizer had poor row estimates to work with.
• Staging tables were cleared with full DELETE statements, which log every row.
• Data passed through intermediate tables that handed it off to the next step with minimal modifications, adding extra writes and reads.

What I Changed

I reworked six core procedures and parts of the surrounding pipeline:

• Split large joins into stages: the biggest gain.
• Broke the large joins into smaller steps using CTEs and temp tables where needed.
• Each step got a simpler execution plan with accurate row estimates.
• Replaced views with temp tables for staging.
• Intermediate results are materialized once instead of recalculated on every reference.
• Temp tables give the optimizer real statistics to plan against.
• Switched DELETE to TRUNCATE.
• Applied wherever no filtering or constraints required row-by-row deletes.
• Removed heavy transaction logging and cleared tables almost instantly.
• Reset identity values on each load, removing the risk of ID overflow.
• Removed unnecessary intermediate tables.
• Rebuilt part of the pipeline so data flows directly to where it is needed, reducing tech debt.
• Cut out entire write/read cycles.

The Result

The nightly runtime dropped from more than 6 hours to about 1 hour 45 minutes, a reduction of roughly 70%. The cubes are refreshed with time to spare, and the job now has room for a rerun if something fails.

Details such as the firm's name, schema, and code are withheld for client confidentiality. The techniques and results described are accurate.