topic Re: Posting large dataset to SQL table in SAS Programming

Posting large dataset to SQL table

SAShole — Thu, 08 Oct 2020 21:52:31 GMT

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

Re: Posting large dataset to SQL table

ChrisNZ — Thu, 08 Oct 2020 20:57:37 GMT

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?

Re: Posting large dataset to SQL table

SAShole — Thu, 08 Oct 2020 21:42:35 GMT

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!

Re: Posting large dataset to SQL table

Reeza — Thu, 08 Oct 2020 21:54:30 GMT

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;

Re: Posting large dataset to SQL table

ChrisNZ — Thu, 08 Oct 2020 22:03:13 GMT

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.

Re: Posting large dataset to SQL table

SASKiwi — Thu, 08 Oct 2020 22:12:33 GMT

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

Re: Posting large dataset to SQL table

jimbarbour — Thu, 08 Oct 2020 23:41:31 GMT

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