Problem Note 63986: The FEDSQL procedure performs poorly or fails with "Out of Memory" errors for tables that are loaded into SAS® Cloud Analytic Services (CAS)
 |  |  |  |  |
PROC FEDSQL might perform poorly or fail with "Out Of Memory" errors under the following conditions:
- You use database tables that contain columns defined with the VARCHAR data type.
- The VARCHAR data type has been defined with a large length but contains short values.
- The database tables have been loaded into a CAS server session.
Diagnosis
If you encounter the issues described above, use the following syntax to view the extended attributes, which are used for database tables that are loaded into a CAS server session:
output log;
table.columninfo /table =' <table-name> ' extended=true;
run;
quit;
The output looks similar to the following:
| Obs | Column | ID | Raw Type | Formatted Length | Length | Format | NFL | NFD | OFFSET | NPOS | MAXBYTES | MAXCHARS | Precision |
| 16 | source_ID | 16 | varchar | 39 | 39 | 0 | 0 | 144 | 144 | 39 | 39 | 16383 |
Database tables that contain columns defined with the VARCHAR data type use an extended attribute called Precision to determine the length of the VARCHAR columns. The MAXBYTES and MAXCHARS values are populated with the length of the longest value found in each column.
In the output, the largest value found in the source_ID column has a length of 39, and the Precision column shows a value of 16383. This output indicates that the Precision value is much larger than necessary.
After you run the code, if you see that the Precision value is much greater than the MAXBYTES value, then you are probably encountering this issue.
Cause
Because CAS uses UTF-8, storing one character might require up to 4 bytes. So, by default, buffer sizes are calculated by multiplying the Precision value by 4. Therefore, VARCHAR columns that have large Precision values but small actual values have much larger buffer sizes than necessary. This behavior causes poor performance or "Out Of Memory" errors.
Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update.
After you install the hot fix, PROC FEDSQL creates internal buffers based on the value in the MAXBYTES and MAXCHARS columns instead of using the Precision column values.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Viya | Microsoft Windows Server 2012 R2 Datacenter | 3.4 | 3.5 | Viya | |
| Microsoft Windows Server 2012 R2 Std | 3.4 | 3.5 | Viya | |||
| Microsoft Windows Server 2016 | 3.4 | 3.5 | Viya | |||
| Linux for x64 | 3.4 | 3.5 | Viya | |||
An update for this issue is available for SAS Viya 3.4. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.4 for Linux Deployment Guide at
http://documentation.sas.com/?softwareId=administration&softwareVersion=3.4&softwareContextId=softwareUpdatesViya on Linux: An update for this issue is available for SAS Viya 3.4. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.4 for Linux Deployment Guide at
http://documentation.sas.com/?softwareId=administration&softwareVersion=3.4&softwareContextId=softwareUpdatesViya on Linux: An update for this issue is available for SAS Viya 3.5. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.5 for Linux Deployment Guide at
http://documentation.sas.com/?softwareId=administration&softwareVersion=3.5&softwareContextId=softwareUpdates| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2019-05-31 09:41:41 |
| Date Created: | 2019-04-05 09:12:56 |