SAS and Redshift Issues

[mcsherrya]

We are working on a technology transformation which involves deploying SAS Enterprise Guide with Redshift. We are using the RedShift ODBC driver and have found some issues. Most notably, we have some tables in RedShift which are large (multiple millions of rows). We noticed that SAS is sending a Select * to bring back all rows from the table before displaying them.

The experience in Enterprise Guide is horrible since this is a large dataset. 

We tried the Tools->Options->Data->Performance to limit the number of rows to 10000 but this doesn't appear to be making any impact. The database is still doing a select * and taking forever to populate the grid on Enterprise Guide.

I have reviewed some of the AWS Redshift ODBC documentation and saw a couple of options such as UseDeclareFetch, SingleRowMode and UseMultipleStatements. I was wondering whether anyone else has come across this issue and been able to resolve it by configuring these parameters in the ODBC connection ini files or even fix it within the configuration of Enterprise Guide itself so that the 10000 limit is passed onto the ODBC driver properly.

Would appreciate any guidance on the above issue.

thanks

Andrew

[JuanS_OCS]

Hello @mcsherrya, Andrew,

perhaps I am wrong but it does not seem to be a problem on RedShift, but on configuration or the software version.

Have you already come across any of these links?

http://support.sas.com/documentation/onlinedoc/guide/blog/WPF_SASEG43.pdf

https://communities.sas.com/t5/SAS-Enterprise-Guide/EG-7-1-very-slow-data-view-performance/td-p/2019... ( @CaseySmith mentions "This issue was fixed in EG 7.12")

https://blogs.sas.com/content/sasdummy/2014/07/09/performance-tips-sas-eg-datagrid/

http://support.sas.com/kb/56/180.html (if data is coming from a large DBMS table, not SAS table, also fixed in EG 7.12)

[mcsherrya]

I do not believe that this solution applies as we are on 7.15 of Enterprise Guide. The documentation links seem to point to a fix in 7.12 but clearly we are beyond that point.

thanks

Andrew

[JuanS_OCS]

Hello @mcsherrya, Andrew,

I understand. Not sure how I can help you further. Have you already contacted your regional SAS Technical Support office?

[mcsherrya]

Appreciate your help. We have a ticket open with SAS but they are being a little slow to respond. I was just checking the community to see whether anyone else has come across and perhaps overcame a similar issue.

[JuanS_OCS]

I see. The notes above helped me with an EG 7.12+ (cannot recall the version) in AWS, but as I found later the problem stood still for later than 7.12. However, and as you said, your case might be completely different.