Writing datasets using OleDB SQL queries. How to CREATE TABLE and compress string fields?

[keV]

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?

Thanks

Kevin

[jakarman]

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.

[keV]

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.