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.

Methodology:

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

Final result:

·         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.

Posted by:

Ritu Lakhani

Email: ritu@mnnbi.com

website: www.mnnbi.com

Phone: 0124-4365845.