Higher Transaction Rates and Better Performance for Filter Queries for a Global Health Client

About the client

The client leads and serves the academic medicine community to improve the health of people. It is a non-profit association dedicated to transforming health through medical research and education, healthcare, and community collaborations.

Challenge

The client witnessed a slew of challenges, as detailed below: 

• There was 98% Oracle database utilization with only 25,000 users.
• The root cause of the issue was custom PDWS filters. These are business requirements that build out ad-hoc queries that request an arbitrary number of columns and join on an arbitrary number of tables. Also, there is no opportunity to index the database for faster retrieval.
• Data is stored in a relational database that represents the ‘source of truth’ for system data. Usually, this data lives in normalized data structures. As the data footprint and the complexity of normalized data grows, there is a need to map from these structures to a searchable data index that can effectively decouple applications’ complex search requirements and performance demands.

LTM’s Solution

LTM’s team worked with the client and executed a comprehensive solution to address their needs. Here are the solution highlights:

• OpenSearch has been chosen as the tool/ solution to allow program users to perform high efficiency ‘application’ searches in Eras for Programs (E4P) as they review, filter, score, rank, and finalize the program cohort.
• A majority of application attributes that are required in the Elastic Search for E4P program user searches are tracked in the legacy MyERAS application.
• Given that several data elements sourced from MyERAS are required for a fully functional modern E4P application ‘search’ function, EA teams finalized an approach to build an interim ‘Data Bridge’ that will synchronize data from MyERAS Oracle database to Amazon Open Search data store in ‘near-real time,’ which will be used in E4P (also known as the forward sync)

Business Benefits 

LTM’s solution led to several benefits for the client, including:

• OpenSearch has better performance over the baseline while using the same test.
• The new solution supports an average of 60 Transactions Per Second (TPS).
• Open Search-enabled PDWS to decouple its Oracle data structures from its search requirements.
• Prior to this, MyERAS applications started every season by rebuilding their database schema from scratch, effectively emptying out all application records. Since the prior season’s data is static and doesn’t change, it is indexed once in Open Search, leading to a highly reliable system that can easily support read-only searches across prior season’s data.