Case Studies / Major Financial Service Provider Cuts Costs in Unexpected Places

A major financial service provider is on a journey to realise data-driven decision making. To achieve this, data needed to be centralised. The financial service provider partnered with Synthesis Software Technologies to create this consolidated view. However, while busy on the project, the Synthesis team found data cost savings in a usually overlooked place.

Challenge

Structured Query Language (SQL) is used to communicate with a database and get data back quickly. Writing good quality SQL is essential in a modern data-driven business. SQL queries are often a low priority. As time goes by, datasets within these databases grow and so does the query execution time and payload size. However, when SQL is written analysed and amended, the results can lead to drastic cost reductions and reduced run and data sizes.

Solution

Synthesis’s Data experts refactored the SQL queries. “It may not be the most glamorous of languages but when done right, the outcome is worth the effort, “explains Deon Schwabsky, Synthesis Customer Success Manager.

With in-depth knowledge of SQL, it is possible to optimise SQL queries using smart built-in SQL Database functionality to reduce “Data-heavy” or “Resource-costly” SQL methods which are quicker and easier to write but require large server capacity and compute power to execute.

Synthesis implemented these “smart efficient functions” and was able to cut down compute calls of the view from 9 to 3 calls.

There is a concept in SQL called a “table scan” – meaning that a whole table is read instead of just a small portion. The financial service provider is using AWS Athena which charges for data retrieval. By being strategic about what was needed from a table, Synthesis cut down on the table scans and therefore cut down on the data scanned.

The functions and code needed to be improved because it was running a number of times per day when it was being refreshed in a dashboard. Synthesis realised that the savings go up exponentially depending on how many times a dashboard would be refreshed once this code was adjusted.

Results

This refactoring reduced run time from 18 minutes to 4 minutes and data sizes being reduced from 177GB to 59GB. This also resulted in a significant cost reduction of approximately 6% in spend per month. The financial service provider can continue on its journey knowing that its data is being optimised on every front.

“SQL should be elegant and beautifully written. Treated with respect it will pay you back. Take care to design and write good query code. Sometimes the quick solutions become permanent, which in turn can become expensive solutions,” concludes Yvonne Geldenhuys at Synthesis. The Synthesis team member has been dubbed a unicorn for her ability to understand and implement both SQL and business strategy.

Our Feed

  • Latest Tweets

  • ⚠ Visit the COVID-19 Corona Virus South African Resource Portal here: www.sacoronavirus.co.za.