Cutting a Nightly Batch Window From Hours to Minutes

A regional insurance carrier's overnight policy-rating batch was routinely breaching its processing window on ASE 15.x. Execution plan analysis and index rework brought it back inside SLA.

Insurance Carrier Query Performance Tuning ASE 15.x T-SQL

6.5h → 58m

Nightly batch runtime

Industry

Regional insurance carrier

5 Weeks

Duration

A Batch Window That Kept Slipping Into Business Hours

SLA Risk

Nightly Rating Run Overran Its Window

The overnight policy-rating and rules-evaluation batch on ASE 15.x had grown from roughly two hours to over six as policy volumes increased — regularly bleeding into the start of the business day and delaying morning reports.

Root Cause Unknown

No Execution Plan Visibility

The client's internal team suspected "the database was slow" but had no systematic way to see which stored procedures were driving the runtime, or whether the plans being chosen were even reasonable for current data volumes.

Stale Indexing

Indexes Built for a Different Era of Data

Core rating tables had been indexed years earlier for a much smaller policy book. Table scans and inefficient joins had crept in as data grew, but no one had revisited the index strategy since the original build.

Profile First, Then Tune — Never the Reverse

Every change was backed by an execution plan comparison and a staging benchmark — nothing was rewritten on a hunch.

Batch Profiling & Procedure Ranking

Instrumented the nightly run to capture per-procedure duration and I/O, ranking the worst offenders by total time contribution rather than guessing which stored procedures were slow.

Execution Plan Analysis

Captured showplan output for the top ten procedures, identifying table scans, poor join orders, and cases where the optimizer was choosing an outdated access path against current data volumes.

Index Strategy Rebuild

Rebuilt covering indexes on the core policy and rating tables, dropped indexes that were no longer used, and updated statistics to reflect current row counts and data distribution.

T-SQL Rewrite of Hot Procedures

Rewrote the highest-impact stored procedures to eliminate cursor-based row-by-row processing in favor of set-based logic, and removed redundant temp table materialization steps.

5 Staging Benchmark & Sign-off

Every change was benchmarked against a production-size data copy in staging before deployment — runtime improvement was measured, not assumed, prior to any production release.

~85% Faster Nightly Batch Window Runtime dropped from roughly 6.5 hours to under an hour, ending a recurring SLA breach without any new hardware or ASE upgrade.

5-Week Turnaround, Not a Multi-Month Rebuild Profiling, index rework, and T-SQL rewrites were scoped and delivered in five weeks — a fraction of the time a full re-architecture would typically demand.

Zero Added Infrastructure Cost The entire gain came from tuning existing ASE 15.x indexes and stored procedures, avoiding the capital cost of a hardware refresh or version upgrade to hit the same SLA.

A Reusable Tuning Playbook The profiling and benchmarking methodology was documented so the client's own team can reapply it as policy volumes keep growing, instead of re-engaging outside help each time.

The Business Impact

Nightly rating batch runtime cut from roughly 6.5 hours to under an hour
Morning reporting consistently available before the start of business, ending the recurring SLA breach
Top ten stored procedures rewritten to set-based logic with peer-reviewed T-SQL
Covering indexes rebuilt and statistics refreshed across core rating tables
Execution plan review became a standing step before any future schema change
No new hardware or ASE upgrade required — the gain came entirely from tuning

"We'd been living with a batch job that ate into the morning for over a year. Watching it finish before 1am for the first time was the moment leadership stopped worrying about it." — OptiSol Sybase Development Lead

Cutting a Nightly Batch Window From Hours to Minutes