SAP Hana view from SQL Server

[former_member250791]

Dear experts,

We have a performance problem with a view on Hana consumed from SQL Server. If we do an select without parameters, the view returns 450.000 registers and take more than 50 seconds on SQL Server. If we aggregated some parameters on SQL Server the view returns 3 registers and takes more than 20 seconds. I doing a trace on Hana and the view always returns the 450.000 registers and then the SQL server do the filter. There is any way that Hana can do the filter and return to SQL Server only the correct registers???

Best regards

[lbreddemann]

How does your SAP HANA view look like?

How does the query look like?

How long does it take to run the query just on SAP HANA?

[former_member250791]

I doesn´t understand the question. This is an example:

From SQL Server

SELECT * FROM SAP_HANAQA_LOCAL.._SYS_BIC."Socovesa.DataMaestra.data.views/SCV_PROVEEDOR"

where LIFNR = '0010000022'

the result is 108 rows on 41 seconds

From Hana Studio

SELECT * FROM _SYS_BIC."Socovesa.DataMaestra.data.views/SCV_PROVEEDOR" where LIFNR =

'0010000022'

The result is:

successfully executed in 29 ms 848 µs (server processing time: 25 ms

691 µs)

Fetched 108 row(s) in 2 ms 919 µs (server processing time: 0 ms 262 µs)

When I do a trace on Hana and execute the select from SQL Server, always return 635.924 records. I think that the filter occurs on SQL Server, and because of this its takes so long

I need a better performance on SQL Server.

[lbreddemann]

Sorry, so you *did* a trace already? On the SAP HANA side?

And how does the SQL from the trace look like then?

If it is the same as you shown above it can only retrieve the same number of records (108) than the SAP HANA version.

[former_member250791]

I don´t know how to do an Trace on SQL Server. SQL Server show only 108 registers, but takes 41 seconds.

I think than Hana returns the 650.000 registers to SQL Server and then, SQL Server apply the filter.

This is normal??

[lbreddemann]

Ok, we need to stop "thinking" (as in "believing" or "not knowing for sure") what the reason for the performance problem is.

You got a hypthesis ("too many records returned and filtered later") and we need to test that.

How did you setup the data access to SAP HANA in MS SQL Server? Is it a ODBC connection?

If so, you could simply setup ODBC SQL tracing.

You can check for long running statements in the expensive statements trace or the shared SQL cache that are issued from the MS SQL server machine.

Then we would see the exact SQL statement the MS SQL server runs against SAP HANA and also how many records are actually returned.

Once we know this for sure we can look into fixing it, ok?

- Lars