DATA Step, Macro, Functions and more

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

Reply
Super Contributor
Posts: 413

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

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,

Super User
Posts: 5,917

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

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.

Data never sleeps
Super Contributor
Posts: 413

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

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,

Super User
Posts: 10,849

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

[ Edited ]

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
;
Super Contributor
Posts: 413

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

Your code really decreased the time, Thanks a lot. 

 

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

 

Thank you

Super User
Posts: 10,849

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

No. I have no idea about it.

Community Manager
Posts: 3,462

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

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.

Ask a Question
Discussion stats
  • 6 replies
  • 140 views
  • 2 likes
  • 4 in conversation