PROC FEDSQL might perform poorly or fail with "Out Of Memory" errors under the following conditions:
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:
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.
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.
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 |