Packing Up and Moving Up to SQL Server
Performance Tuning and Troubleshooting
The Challenge: Slow XML Processing Performance
OOPFactory is a popular open source data parsing tool that supports the HIPAA X12n EDI transaction sets. The tool was part of a solution to move hospital and provider claims into SQL Server. Since the tool transforms X12n EDI data into XML documents, SQL Server's XML processing features were used to transform and load the data into SQL Server. The client complaint? Significant performance issues prevented it from working at scale.
The Cimetre
Solution
Through code profiling and SQL server tracing, we were able to determine that the pain point was with the use of XML data type methods introduced in SQL Server 2012. After analyzing the workload, Cimetre advised the use of OpenXML, a well-established feature of SQL Server. The client realized a 10-fold increase in performance after refactoring.
The Challenge: Transactional Replication Delay
This client used transactional replication to populate a read-only data replica with millions of records daily. During peak data loading periods, the replication backlog grew to several hours, even though the replica's hardware was similar to the master — and both systems resided in the same data center.
The Cimetre
Solution
By analyzing the data traffic on the replica using SQL Profiler, we were able to isolate the problem: poorly-performing select statements related to replication. Judicious application of indexes to some of the replication system tables completely eliminated the delay in replication.