by Jason Williscroft
Markit EDM at its heart is no more than a thin layer of abstraction atop a SQL Server database.
Every HotQuant-led client training session begins with this refrain, and it is repeated daily across the life of every HotQuant engagement. Its purpose is not to denigrate MEDM as a product, but to highlight the fact that the immensely useful behaviors encapsulated by MEDM components are realized almost entirely as SQL Server database objects and data transfer processes. SQL Server ships with a formidable array of tools aimed at the analysis and optimization of SQL Server performance, and every one of these tools has a place in the MEDM developer’s arsenal.
Given an understanding of what an MEDM component is doing under the hood, performance optimization is straightforward: query optimization and the creation of appropriate indices will generally do the trick. In an implementation comprising dozens of user tables and hundreds of MEDM components, the critical question is not HOW to optimize a particular component or object, but WHICH ones to optimize. Wrong answers to this question can form a rabbit hole of epic proportions.
In the summer of 2013 HotQuant faced a difficult problem in MEDM performance optimization. A large hedge fund was constructing a trade flow pipeline with HotQuant’s help, and the client’s internal service level agreements were so tight that the implementation as written was exceeding acceptable trade processing rates by a factor of three. The client was unhappy, and the clock was ticking.
Over a two-week period, HotQuant was able to improve trade pipeline throughput by a factor of six, leaving a comfortable operating margin against the client’s SLA. The solution was based on three key observations:
- As noted above, the fundamental question is not how to optimize, but where.
- MEDM’s internal logging engine, while somewhat cryptic, is thorough to a fault.
- Since MEDM is at heart a thin layer of abstraction atop a SQL Server database, the product is inherently capable of introspection. In other words, it can query its own logs.
HotQuant wrote a recursive query that used MEDM’s internal logs to traverse a given Solution’s historical invocation tree, expressing each component’s actual execution time in terms of the overall execution time of the component that invoked it: a direct measure of each component’s impact on performance. Then HotQuant directed the client to run a series of datasets representing twice the anticipated peak loading over the expected lifetime of the implementation. After all trades had processed, HotQuant ran the new recursive query and sorted the result, identifying the small set of components that accounted for over 90% of the pipeline’s overall run time.
After optimizing these components, HotQuant iterated the process twice more, ultimately producing the result related above. Then, for good measure, HotQuant integrated the new performance analysis tool into our existing MEDM-based process monitoring dashboard, providing an open window on performance bottlenecks that subsequent clients have found quite useful indeed.
The creation of this tool expresses a core HotQuant principle with application well beyond performance optimization: there is nothing sacred about shrink-wrap.