Case Studies

Financial Institution · Regulatory Compliance · C#/.NET & SQL Server

Building an automated SEC securities lending reporting system from scratch

A major financial institution needed to comply with new FINRA securities lending reporting requirements. They had an existing SFTR system but nothing for SEC. The deadline was tight, the business logic was complex (20+ reporting scenarios), and there was zero room for error — T+1 deadlines with potential fines for non-compliance.

What I built

I designed and implemented the complete system over several months — database schema, C# processing engine, JSON file generation, SFTP transmission, and an 881-test automated test suite.

Normalised database schema with dedicated SEC tables, linked to existing SFTR data to avoid duplication
Event-driven C# processing engine handling 20+ distinct reporting scenarios
MD5 hash comparison for detecting actual data changes vs. redundant updates
Intelligent report type determination engine mapping SFTR actions to FINRA report types
BZip2-compressed JSON file generation with SFTP auto-transmission
Batch processing schedule aligned to trading hours (13:30, 16:30, 23:00)
881 automated xUnit tests with parallel execution and CI/CD in Azure DevOps

100%

On-time compliance

~40 hrs

Manual work eliminated weekly

<5 min

Batch processing time

10,000

Daily transactions

Zero

Compliance violations

C# .NET 6.0 SQL Server 2019 Dapper ORM xUnit Azure DevOps Hangfire SFTP BZip2

Financial Institution · Regulatory Reporting · SQL Optimisation

Reducing SFTR report generation from 15 minutes to under 2 minutes

When I first looked at the SFTR reporting procedure, I could see the problem immediately in the execution plan. The main aggregation query was performing 47 table scans across tables with millions of rows. The stored procedure had grown organically over 4 years — every new regulation meant another subquery bolted on. Nobody had ever looked at the full picture.

What I did

I conducted a comprehensive analysis of the reporting pipeline, focusing on execution plans, schema design, and data access patterns. The optimisation strategy included:

Replaced 12 correlated subqueries with indexed views for frequently accessed data combinations
Implemented incremental processing — only recalculating data that changed since the previous run
Refactored nested queries into well-indexed temporary tables with filtered indexes
Moved business logic calculations to persisted computed columns
Added carefully designed composite indexes aligned with actual query patterns
Implemented parallel processing for independent data segments

The entire refactoring was done with zero production disruption. Extensive reconciliation testing verified that the new process produced identical results to the original.

87%

Processing time reduced

<2 min

Report generation

70%

Lower CPU usage

Zero

Production issues

SQL Server 2019 T-SQL Indexed Views Filtered Indexes Query Tuning C# Integration

What a Typical Audit Looks Like

A real performance audit walkthrough

Here's what happens when you book a Standard Audit. This is a composite example based on common patterns I see across clients:

The process

Client runs my diagnostic scripts (takes 5 minutes, read-only, safe for production)
I receive the output files and begin analysis within 24 hours
I review wait statistics — in this example, PAGEIOLATCH waits are dominating, pointing to I/O bottleneck
Execution plan analysis reveals a frequently-run query doing a clustered index scan on a 50M-row table instead of a seek
The missing index DMV shows 3 indexes that would have a high estimated impact — but I validate them against actual workload patterns before recommending
Configuration review flags MAXDOP at default (0) on a 16-core server, and cost threshold at the default 5
I write the report with specific T-SQL CREATE INDEX statements, the MAXDOP and cost threshold values to set, and estimated impact for each change
On the 30-minute call, I walk through the findings and answer questions about implementation order and risk