02-09-2017 12:11 PM
We are using SAS EG to query base tables and views in a remote SAP HANA environment. When attempting to build queries using the query builder we are experience significant latency and occasional crashes of the EG client when attempting to join multiple tables. This occurs even after disabling automatic query validation and automatic table joining. (See attachment.)
Note that the problem is not slow query response time. Once the query is built, it returns data in a reasonable timeframe. But building/modifying the query is a struggle because of the latency/instability issues with the Query Builder.
I opened a SAS track on the issue, but their only suggestion was to write an explicity pass-though SQL statement instead. Hoping someone had experienced a similar issue and had found a resolution that allows them to use Query Builder effectively.
02-09-2017 03:59 PM
I don't have a direct answer to your question, but I wanted to highlight the issue from a different prospective, maybe that would give you an alternative way to achieve your goal.
Without knowing your SAP-Hana Libname settings, there are two Libname statement options I would recommend you revisit
If I'm not mistaken, typicall EG query builder, in fact, any GUI query builder, would try to extract list of Tables & Columns from the under lying dicitonary tables, and if you are not specifying a specific schema ownning the tables, you could be retrieving a list of all the tables within your SAP-Hana Instance!
Check out this SGF paper "EMPOWERING SAS® USERS ON THE SAP HANA PLATFORM", it may provide some hints and tips that can help with your issue.
02-09-2017 04:42 PM
Thanks for these suggestions!
The tables I am joining all fall within the same schema in SAP HANA, and I am specifying the schema in my libname statement. However, there are very many tables in that schema, so that could be a problem.
I will review the other libname statement options you have identified and take a look at that paper.
02-09-2017 08:21 PM
What version of EG are you using? Do you have access to other versions to see if you get the same problem. Does it happen with all SAP HANA / EG users or only some? Has it always been a problem or has it only started recently?
02-10-2017 11:09 AM - edited 02-10-2017 11:30 AM
We're using EG 7.10/SAS 9.4 on Windows 7. Sorry, should have meantioned that.
I don't have access to any other versions to compare. Problem seems to affect all users. (I haven't polled them all, but I have yet to talk to a user who hasn't encountered it.) We only recently deployed EG and are just now starting to use Query Builder in this way, so there's not alot of history behind this problem.
I've explored the libname statement options Ahmed suggested, but so far I'm not seeing anything that seems to address this problem directly. Continuing to look at the libname documentation.
P.S. Here is my libname statement, in case it helps:
/* schema: PUBLIC */
libname v_public saphana
server="xxxxxxxxx" encrypt=YES port=#####
/* preserve_col_names=YES */