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

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

keV — Thu, 24 Jul 2014 11:10:24 GMT

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

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

jakarman — Thu, 24 Jul 2014 11:55:59 GMT

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.

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

keV — Thu, 24 Jul 2014 14:18:58 GMT

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.