topic Re: Optimizing the job to load data to SQL Server using SAS Enterprise Guide in SAS Programming

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

Akshay1002 — Wed, 29 Jul 2020 09:07:45 GMT

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;

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

ChrisNZ — Wed, 29 Jul 2020 09:54:20 GMT

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

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

Akshay1002 — Wed, 29 Jul 2020 16:52:24 GMT

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

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

SASKiwi — Wed, 29 Jul 2020 20:38:46 GMT

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.

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

Akshay1002 — Mon, 03 Aug 2020 05:10:23 GMT

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.

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

ChrisNZ — Mon, 03 Aug 2020 05:18:24 GMT

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.

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

Patrick — Mon, 03 Aug 2020 05:37:47 GMT

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;