Query reduction implementation example: large hospital chain in Gurgaon
Problem: the nightly process of the hospital chain was taking time because of which the SLA(performance parameter) of the CTO of the firm was not been met. The ETL team was advised the bottleneck sql by the end client manager.
Methodology: the end client had developed the code using cursor logic. The ETL team developed the code using set based logic in order to optimize the usage of RAM. The given process has the following benefits for the end client:
· Optimum usage of RAM
· Lower implementation times
· Error logging and error handling
· Incremental data loads after the initial data loads.
· Audit of the transformation process for the end client.
The ETL team adopted the Inmon methodology for resolving the same. The cursor logic was reverse engineered using the set based system. The following were the methodologies adopted by the ETL team.
· Data normalization: error logging, data audit, incremental data load and optimum usage of RAM.
· Data transformation: converting the cursor logic to set based logic
· Data cleansing: obtained from the cursor logic
· The output of the set based system was matching with the cursor logic output.
· The execution time was reduced by more than 80(eighty) percent.
Suggested next steps for the prospect:
· Send the bottleneck sql to the ETL team.