02-12-2018 06:34 AM
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?
02-12-2018 06:52 AM
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.
02-12-2018 11:27 AM
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?
02-13-2018 04:49 AM - edited 02-13-2018 04:52 AM
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 ;
02-16-2018 09:37 AM
Your code really decreased the time, Thanks a lot.
By the way, is it possible increase the limit of INSERTBUFF option?
02-16-2018 10:49 AM
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.