topic Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server? in SAS Programming

How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

turcay — Mon, 12 Feb 2018 11:34:05 GMT

Hello everyone,

I try to move data sets from SAS server to MSQSql Database Server, however, it takes 5-6 hours. Those data sets are very large data sets, one of them approximately 60 GB. To decrease the time, I tried to use INSERTBUFF option as below,

LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas USER=user PASSWORD="*******" INSERT_SQL=YES INSERTBUFF=32767;

But it decreased the time approximately 3 hours so I am looking for a method to decrease the time less than half an hour, I mean as fast as possible. Can somebody lead me about this process?

Thank you,

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

LinusH — Mon, 12 Feb 2018 11:52:26 GMT

There have ben several similar threads on forum with similar issues, first search here.

Short, there are more to this than simple libname options.

First you have any target DBMS behavior/DB design.

Also, network plays a role.

Form a processing perspective, I should start to investigate using bulk-load.

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

turcay — Mon, 12 Feb 2018 16:27:08 GMT

Hello,

Is it possible to share some of the links which mentioned about forum as above.

And also I wonder, there is a limit(32767) for InsertBuff, is it possible to increase it?

You also mentioned about Bulkload, I found a similar link as below,So I need to type Bulkload=Yes, right?

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002105630.htm

Thank you,

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

Ksharp — Tue, 13 Feb 2018 09:52:52 GMT

Try some options.

options dbidirectexec bufno=100 bufsize=128k ;
LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas U
SER=user PASSWORD="*******" INSERT_SQL=YES 
INSERTBUFF=32767
readbuff=10000
bulkload=yes
dbcommit=10000
;

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

turcay — Fri, 16 Feb 2018 14:37:25 GMT

Your code really decreased the time, Thanks a lot.

By the way, is it possible increase the limit of INSERTBUFF option?

Thank you

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

Ksharp — Fri, 16 Feb 2018 14:49:04 GMT

No. I have no idea about it.

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

ChrisHemedinger — Fri, 16 Feb 2018 15:49:26 GMT

According to the documentation, the limit for INSERTBUFF depends on the database. You might need to get that from Microsoft doc. You can certainly try a higher value, such as "max int" (2147483647) -- see what happens.

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

turcay — Tue, 11 Sep 2018 12:18:45 GMT

How can I write same options for Oracle connection?

I wrote the following libname statements;

Libname orc oracle path=ed schema=edb user=u1234 password="*****" but the other options gave errors;

Thank you

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

Ksharp — Tue, 11 Sep 2018 12:59:27 GMT

Sorry. I can't help you. Talk to sas technology support ?

If you get error, then some option is not suited for ORACLE connection.

Re: How to - Decrease the Time While Moving Data Between SAS Server and MSSQL Server?

LinusH — Tue, 11 Sep 2018 13:17:50 GMT

SAS/ACCESS to Oracle documentation lists available libname options.