I'm trying to insert into a SQL Server table from SAS SPDS table using proc sql.
It is running, but very slow.
~35 million records (~5 GB datasize) in 5 hours.
connection is via ODBC
Is there anything I can tune to make it go faster?
========================== code ==========================
/*****************/
/* SAS SPDS Libs */
/*****************/
LIBNAME src sasspds host="sas01" serv="5190" user="spdsadm" ip=yes schema= "SRC" password="XXXX" compress=yes;
/*****************/
/* SAS SPDS Libs */
/*****************/
libname SQL ODBC dsn=DWSQLServer user=sas password=XXXX schema=dbo;
%let spdssize = 49376m;
%let path=./logs;
%let LogFile="&path/post_insert_prod_model_scores_to_sql.log";
%let LstFile="&path/post_insert_prod_model_scores_to_sql.lst";
Proc printto log=&LogFile print=&LstFile new; run;
options obs=max;
proc sql;
insert into sq.stg_model_score
(id,
model_output_id,
model_score,
model_decile_level,
source_id,
score_date,
scored_by
)
select
id,
model_output_id,
model_score,
model_decile_level,
source_id,
model_scored_date,
model_scored_by
from source.SCORE_PROD_OUTPUT;
run;
quit;
