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

[turcay]

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,

[LinusH]

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.

[turcay]

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,

[Ksharp]

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
;

[turcay]

Your code really decreased the time, Thanks a lot. 

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

Thank you

[Ksharp]

No. I have no idea about it.

[ChrisHemedinger]

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.

[turcay]

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

[Ksharp]

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

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

[LinusH]

SAS/ACCESS to Oracle documentation lists available libname options.