BookmarkSubscribeRSS Feed
mcsherrya
Fluorite | Level 6

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

 

5 REPLIES 5
JuanS_OCS
Amethyst | Level 16

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
Fluorite | Level 6

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
Amethyst | Level 16

Hello @mcsherrya, Andrew,

 

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

mcsherrya
Fluorite | Level 6

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
Amethyst | Level 16

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.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1455 views
  • 1 like
  • 2 in conversation