Optimizing the job to load data to SQL Server using SAS Enterprise Guide

[Akshay1002]

Hello,

I'm bulk loading approximately 60 million records to SQL database using EG and it's taking 5 hours to have the job run successfully.

Please suggest me the tips to optimize the run time.

Environment - SAS 9.4 M6, Grid

Here is the code:

options fullstimer;

libname abc odbc noprompt= "Driver=xxxxx; Server=yyyyy; database=zzzzz; Trusted_connection=Yes" READBUFF=32767 INSERTBUFF=32767 DBCOMMIT=32767 schema=pqr;

libname xyz odbc noprompt= "Driver=xxxxx; Server=yyyyy; database=ggggg; Trusted_connection=Yes" READBUFF=32767 INSERTBUFF=32767 DBCOMMIT=32767 schema=pqr; 

proc sql;

drop table xyz.table_name;

quit;

data xyz.table_name;

set abc.table_name;

run;

[ChrisNZ]

You are copying from SQL Server to SQL Server. Can't you use explicit pass-through, i.e send pure SQL Server code?

[Akshay1002]

Yes,
We did try the pass through option. The results are still the same.

[SASKiwi]

Please post your passthru code.

Your SAS SQL version indicates you are reading all of your SQL Server data into SAS, then immediately writing it back to SQL Server. With passthru you should be able to read data directly from one SQL Server table and write it into another without extracting it to SAS.

[Akshay1002]

I'll post the pass through code soon. Sorry about the delay.

Is it true that we need SAS/ACCESS to SQLServer engine inorder to bulkload data into SQL databases? And we don't have it in our environment.

[ChrisNZ]

You don't need BULKLOAD here as the source data is not SAS. You should be able to have all the data stay within SQL Server's realm.

[Patrick]

Ideally don't move the data from SQL Server to SAS to SQL Server but just insert from one SQL server table into another SQL server table - and as you don't move data in and out of SQL server you also don't need bulk-load.

Untested but some code like below could do the job. Basically: Develop your code directly in SQL and then just copy/paste the working script into a SAS pass-through Execute block.

libname xyz odbc noprompt= "Driver=xxxxx; Server=yyyyy; database=ggggg; Trusted_connection=Yes" schema=pqr; 

proc sql;
  connect using xyz;
  execute 
  (
    INSERT INTO pqr.TargetTable
       SELECT *
       FROM zzzzz.pqr.SourceTable
       ;
  ) by xyz
  ;
quit;