Posting large dataset to SQL table

[SAShole]

I'm trying to create a table in a SQL database using proc SQL. My dataset is 200K records and has twenty variables. However, some of them are free text variables and have huge informats like $3000 -- So my final file ends up being about 2GB. I don't have any meta information on the source file so I'm not able to say if I can save space anywhere. The problem is that the table takes too long to load, about 5 hours run time.

Am I able to adjust the auto-growth? Or check what the default is?

Would the explicit SQL pass-thru be any help?

Also, not helping things is the SAS Server is in the EU and SQL Server is in the US.

I haven't tried adding these options but maybe they would help:

options compress=yes sastrace=',,,d' sastracelog=log notsuffix;

My code looks like this:

libname DB odbc noprompt="driver=XXX server=XXX database=XXX";

proc sql;
          create table DB.BigTable;
          as select *
          from work.BigDataSet;
quit;

 Edited post to include information from @ChrisNZ  questions

[ChrisNZ]

What's the issue?

Is the process too slow?

Is the table too large? 

How do you talk to SQL Server?

SAS/ACCESS Interface to ODBC?

SAS/ACCESS Interface to OLEDB? 

SAS/ACCESS Interface to Microsoft SQL Server?

[SAShole]

Hi Chris,
The table takes to long to load into SQL. Run time is about 5 hours.

I'm using SAS/ACESS interface with ODBC.

Thank you!

[Reeza]

What happens if you don't use SAS for the load, ie via SQL Server directly? Is it set up with the bulkload option?

Or try a PROC COPY instead? How long does that take instead of a SQL step?

proc copy in=work out=db;
select bigDataSet;
run;

[jimbarbour]

Well, @SAShole,

Part of the problem is that you're using ODBC which isn't typically the best.  DB specific drivers are typically faster.  But of course we have what we have.

With Hive with ODBC, I found setting the INSERTBUFF and DBCOMMIT both in conjunction with one another to 32767 was the fastest -- significantly faster than not setting them, but I can't say what setting would be best for your situation.  @SASKiwi and @ChrisNZ  helped me tremendously and walked me through that process, so you're in good hands there.

Jim

[ChrisNZ]

See if the variables really need to be that long.

ODBC does not have a FASTLOAD option, but try adding  insertbuff=10000  to your ODBC LIBNAME statement.

Tweak the figure to see what's fastest for your data.

[SASKiwi]

The other option which might help is DBCOMMIT. Try similar values with that too.