Help using Base SAS procedures

very slow performance from proc sql

Reply
N/A
Posts: 0

very slow performance from proc sql

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;
PROC Star
Posts: 1,566

Re: very slow performance from proc sql

Have you tried the BULKLOAD= and BULKCOPY= options?
N/A
Posts: 0

Re: very slow performance from proc sql

support helped.

bulkload= is not available on UNIX
insertbuff= works!

thanks
Ask a Question
Discussion stats
  • 2 replies
  • 142 views
  • 0 likes
  • 2 in conversation