very slow performance from proc sql

deleted_user — Mon, 08 Jun 2009 19:50:23 GMT

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;

Re: very slow performance from proc sql

ChrisNZ — Tue, 09 Jun 2009 02:20:34 GMT

Have you tried the BULKLOAD= and BULKCOPY= options?

Re: very slow performance from proc sql

deleted_user — Tue, 09 Jun 2009 15:31:30 GMT

support helped.

bulkload= is not available on UNIX
insertbuff= works!

thanks

topic Re: very slow performance from proc sql in SAS Procedures

very slow performance from proc sql

Re: very slow performance from proc sql

Re: very slow performance from proc sql