Over the last 8 months I have been working with a team of people to move a very large SAP BW system to HANA. Overall we moved data into a 15TB BW on HANA environment and tested almost 100 of the most used queries. We also tested HANA vs. BWA and HANA data load performance of over 500 jobs. This was a test to see if it could be done and to get some performance benchmarks from testing a real system with real queries. In this blog we look at the real performance benchmarks. By Dr. Berg
BW on HANA Query Performance Vs. Oracle – 16.6 times faster
We tested almost 100 queries from seven business areas in the company and found that we had an average of 16.6 times faster query run-times. This meant that a query that took on average 64 seconds on Oracle now ran in 4 seconds on HANA.
However, we also saw even more spectacular performance in some areas. For example, some queries that took over 348 seconds on Oracle was running in 1.19 seconds on HANA (292 times faster). About 10% of our queries tested were at least 50 times faster on HANA.
Actually, queries that predominantly used simple sub-totals, group-buy and large data reads, performed the best in HANA. This was common in Finance, which had on average 28 times faster queries on HANA than on Oracle.
BW on HANA Load Performance Vs. Oracle – 43% faster
We executed over 530 data load jobs in our test. Many were executed multiple times. We did not make any changes to the SAP on HANA loads, even when we could have got even faster data loads by adding hints on slow ABAP routines.
Overall we saw 43% faster data loads from the process chains. The loads within BW for Acquisition, Propagation, Transformation and Reporting layers in LSA, went from running 37 thousands minutes (overall) to 21 thousand minutes. Naturally, the jobs were running in parallel so the overall time was much shorter. We saw that about 19% of the performance gain came from data activations.
Finally, we redesigned two data flows to see what performance again we would get from removing layers in LSA to the new LSA++ architecture for HANA. In these scenarios we saw data load times decrease from over 6.2 hours on LSA on Oracle to under 2.1 hours for LSA++ on HANA, a 67% performance gain.
BW on HANA Query Performance Vs. BWA – 64% faster
We also tested a set of queries that leveraged BWA on the traditional BW system vs. BW on HANA. This was predominantly very large queries with substantial results sets and several group-by, totals and sub-totals.
On the BWA system we had an average query performance of 43.6 seconds. On HANA the same queries with the same selection criteria, ran in 15.0 seconds. That was a 64% performance gain.
Overall these numbers are very impressive and should put to rest the debate on what HANA can handle very big-date (we had 40 TB), if benefits also extends to data loads (it does) and if HANA is any better than BWA (it is).
PS! The HANA system consisted of 30 servers from HP (DL-980) with 512 GB RAM and 40 cores each.
Next time we will look at more of the lessons learned from HANA database migrations of very large BW systems and what you should look out for....