How to Improve Query Performance in SAP Datasphere when working with remote tables

[rebeca_elvetia]

Hello Experts

We have been working with SAP Datasphere, trying to recreate models previously built in MS SQL Server.

Because of that we chose as our main data product the SQL Views. The model consists of building SQL Views upon other SQL Views built upon Remote Tables.

Due to our company politics, we can only access data remotely, no data has to be stored in the cloud. Our data strategy is federation only.

But because of that, we have been having some issues. When previewing data, we pass the 3 minutes given by the platform and we can't preview.

I read this blog for advice on improving query performance: https://community.sap.com/t5/technology-blogs-by-sap/8-ways-to-increase-your-query-performance-in-sa... 

We tried running in analytical mode, that I believe is the same as activating the OLAP hint for SQL View (we tried with just one view) and the results are not satisfactory. We still can't preview data. Do we have to enable the analytical mode in all our views to see the improvement? (Our views are built upon views)

I was thinking maybe if we turn on "In-Memory Storage" for the remote tables we use to creating the SQL Views it could improve, but would that work for remote tables?

Replication is not an option for us. Our remote tables come from SAP HANA OP, integration via DPAgent and SDI.

Are there other ways to improve performance?

Thanks in advance

Rebeca

[JulianJuraske]

Hello,

as I understood you have a SAP Source(S4 or BW4) System (OP) operating on a SAP HANA Database.
So you should have the Data Stored hopefully in Memory Tables ;).

Since you are not allowed to replicate the Data into the DSP you have five Options only:

1. Boost the Performance of the delivering source
   Meaning if your remote tables is a virutal Object in the Source that performance havy calculations and therfore requires time you should think of persisting the outcome of the "Virtual" Result if that is an option).
2. Create Statistics on your remote Table
   In the Data Integration Monitor you can create Statistics for Remote Tables that will enable the SAP HANA Optimizier to optimize the SQL Statement send the source SAP HANA DB.
   
   
3. Restrict the Outcome/Result
   If you have created Statistics and checked the performance of the source object itself it might be that the result you deliever is to granular (e.g. Displaying all Order since 2019).
   If you do this you can optimize whatever you want, if you 10 Mio Records have to be transfered and displayed this will result in a Timout
4. Review your Datamodell
   Meaning check the JOIN Conditions, Filters and everything if it is optimized.
   e.g. You have Table 1 (ORDERS) that you Join with TABLE 2 on ORDER ID Level, the System will have to first load all ORDERS (10 Mio Records ) to than perform the JOIN.
   If you join on a more Aggregated level, the SQL Optimizier will do the Aggregation in the Source System and for example only return 400.000 Records as a Result that than have to be Joined with Table 2.
   Also Working with Assoications will boost your performance compared to hard used joins, as associations (Dimension, Text, Hierarchy ect.) will only be executed if required.
5. if you need more detailed help I would suggest you get a Consultant

I hope this help's 😉.

BR
Julian