Homepage > Portfolio > Extract Process Optimization

Extract Process Optimization

Optimization of the client’s general ledger extract process, which works with large datasets from different sources.

Customer

Patient Safety is a healthcare discipline that emerged with the evolving complexity in healthcare systems and the resulting rise of patient harm in health care facilities. It aims to prevent and reduce risks, errors and harm that occur to patients during provision of healthcare. A cornerstone of the discipline is continuous improvement based on learning from errors and adverse events.

To ensure successful implementation of patient safety strategies; clear policies, leadership capacity, data to drive safety improvements, skilled health care professionals and effective involvement of patients in their care, are all needed.

US leader in customized insurance, claims and patient safety and risk solutions for physicians, surgeons, dentists and other healthcare professionals, as well as hospitals, senior care facilities and other healthcare providers turned to Azati to streamline the process of extracting data from the client’s general ledger.

Objective

A large volume of data poses new challenges, such as overloaded memory and algorithms that never stop running. It forces you to adapt and expand your repertoire of techniques.

Poor query design is one of the top SQL Server performance killers. Even with good database design, no frequent recompilations, and no other SQL performance killers, poor query design can severely degrade performance.

Query performance also depends on data volume and transaction concurrency. Executing the same query on a table with millions of records requires more time that performing the same operation on the same table with only thousands of records.

It was noticed that one SQL-query was performing poorly and required optimization. The task was to find out the reasons which had a negative impact on the system performance and eliminate them.

SOLUTION

The problem was revealed during system performance monitoring. While building the query execution plan, the optimizer was more likely to choose a full table scan instead of more efficient use of existing indexes. We also noticed a whole class of the queries having the same behavior.

At the next step, we conducted a root cause analysis of the issue, which showed that the system statistics weren’t valid and they failed to represent the real performance of the system.

The elaborated solution to this system issue contained the following:

  • Identifying the time intervals for gathering statistics;
  • Gathering and validating system statistics values for the database;
  • Finding the balance, since the Database serves both batch (DSS) and online transaction processing (OLTP);
  • Gathering daytime, night-time and 24-hour system statistics for the databases, when the workload is expected to be typical of the system;
  • Analyzing the gathered values;
  • Publishing new system statistics.
  • As the result, the number of total direct path read events decreased twice. Therefore, some of the bottleneck processes speeded up manifold – from 3 hours run time in average to 10-15 minutes.

Technologies

 
 

SCREENSHOTS