SUPPORT / SAMPLES & SAS NOTES
 

Support

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)

DetailsHotfixAboutRate It

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:  

cas mySession ;
proc cas;
output log;
table.columninfo /table ='  <table-name>  ' extended=true;
run;
quit;
cas mySession terminate;

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 FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS ViyaMicrosoft Windows Server 2012 R2 Datacenter3.43.5Viya
Microsoft Windows Server 2012 R2 Std3.43.5Viya
Microsoft Windows Server 20163.43.5Viya
Linux for x643.43.5Viya
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.