07-24-2014 07:10 AM
I am using the SAS IOM OleDB provider to create and populate SAS tables via execution of SQL statements.
All is working fine, but, for creation of string fields, I am defaulting to a length of 400 because I cannot know the maximum string length in advance.
This results in very large disk space usage for string fields.
One option is to somehow set COMPRESS = CHAR as part of my CREATE TABLE execution, or some other command I can execute on the connection.
Does anyone know how to do this, or can suggest anything else to help?
07-24-2014 07:55 AM
SAS(R) 9.3 SQL Procedure User's Guide (dataset options). Did you try it coding as a SAS dataset option?
IT will do RLE optimizing causing some overhead in the CPU but of you have many of those the gaining in IO could be a far more better trade off.
07-24-2014 10:18 AM
I did not realise it was as simple as changing "CREATE TABLE (" to "CREATE table (compress=char, " into my CommandText.
On my test table, what was 312Mb is now 12 Mb (262,144 rows with 3 string fields). Very impressive size reduction, and it writes20% faster too.