append data to sql data

[mariopellegrini]

Good morning. A proc append in a SQL data library takes a very long time to append data. However, the same data on a basic sas library takes a few seconds. I report the 2 example logs.

I'm asking for suggestions on which method is better to hang data on a Sql Server data library from sas:

proc append base = LIB_SAS.TAB1 data = backup.WXGC21K force nowarn;

run;

NOTE: Appending BACKUP.WXGC21K a EDWH_ODS.TAB1.

NOTE: FORCE is specified, so dropping/truncating will occur.

NOTE: There were 16945246 observations read from the data set BACKUP.WXGC21K.

NOTE: 16945246 observations added.

NOTE: The data set EDWH_ODS.TAB1 has 16945246 observations and 4 variables.

NOTE: PROCEDURE APPEND ha utilizzato (tempo totale di elaborazione):

      real time           14.97 seconds

-------------------------------------------------------------------------------------------------

proc append base = LIB_SQL.TAB1 data = work.WU98D7R force nowarn;

run;

NOTE: Appending WORK.WU98D7R a LIB_SQL.TAB1.

NOTE: FORCE is specified, so dropping/truncating will occur.

NOTE: There were 16945246 observations read from the data set WORK.WU98D7R.

NOTE: 16945246 observations added.

NOTE: The data set LIB_SQL.TAB1 has . observations and 4 variables.

NOTE: PROCEDURE APPEND ha utilizzato (tempo totale di elaborazione):

      real time           2:56:25.66

      user cpu time       2:21.54

      system cpu time     4:19.78

      memory              593.34k

      OS Memory           18784.00k

      Timestamp           20/06/2023 05:34:11 p.

      Step Count                        243  Switch Count  0

[HenryKobus]

I do not know much about the environment you are working in. I assume that proc append just take a "few" observations and gives it to SQLServer who appends them. After doing so maybe the SQLServer starts re-creating  an index or so, waits for completion and after that accepts the next package.

I would try to get the data to be appended in the SQLServers database and let the SQLServer doing the append.  Something like this  : 

proc upload data = backup.WXGC21K out = LIB_SQL.WXGC21K;run;

proc sql; 
	connect to  ODBC (dsn="MySQLServerConnectString" schema=dbo);
		execute (INSERT INTO TAB1  
					SELECT *
					FROM dbo.WXGC21K) by ODBC;
	disconnect from ODBC;
quit;

proc sql; 
	connect to ODBC (dsn="MySQLServerConnectString" schema=dbo);
		execute (Drop Table WXGC21K) by ODBC;
	disconnect from ODBC;
quit;;

or if you have licenced SAS/Interface for Access to SQLServer you may try this:

proc upload data = backup.WXGC21K out = LIB_SQL.WXGC21K;run;

proc sql; 
	connect to  sqlsvr (user="Domain\UserID" password="MyPassword" datasrc="MySQLServerConnectString" schema=dbo);;
		execute (INSERT INTO TAB1  
					SELECT *
					FROM WXGC21K) by sqlsvr;
	disconnect from sqlsvr;
quit;

proc sql; 
	connect to sqlsvr (user="Domain\UserID" password="MyPassword" datasrc="MySQLServerConnectString" schema=dbo);
		execute (Drop Table WXGC21K) by sqlsvr;
	disconnect from sqlsvr;
quit;;

[mariopellegrini]

Thank you. But I can't connect from proc sql.

LIBNAME EDWHDSST SQLSVR Datasrc=EDWHDSST SCHEMA=DBO AUTHDOMAIN="SQLSRVAuth" ;

NOTE: Libref EDWHDSST was successfully assigned as follows:
Engine: SQLSVR
Physical Name: EDWHDSST

But this proc sql:

proc sql;
connect to SQLSVR as mydb (Datasrc="EDWHDSST" SCHEMA="DBO" AUTHDOMAIN="SQLSRVAuth" );
execute (SELECT * FROM TABSQL) by sqlsvr;
disconnect from sqlsvr;

returns the following error:

27 proc sql;
28 connect to SQLSVR as mydb (Datasrc="EDWHDSST" SCHEMA="DBO" AUTHDOMAIN="SQLSRVAuth" );
NOTE: Credential obtained from SAS metadata server.
ERROR: Invalid option name SCHEMA.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

or using username and password:

proc sql;
connect to SQLSVR as mydb (Datasrc="DBO" user=user password=xxxx);
execute (SELECT * FROM TABSQL) by sqlsvr;
disconnect from sqlsvr;
quit;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
specified

[HenryKobus]

The schema is needed in LIBNAME statement. For pass through the tablespace follows the FROM statement and then a period + table name:

proc sql;
connect to SQLSVR as mydb (Datasrc="EDWHDSST" AUTHDOMAIN="SQLSRVAuth" );
execute (SELECT * FROM dbo.TABSQL) by sqlsvr;
disconnect from sqlsvr;

[mariopellegrini]

Okay. Thank you.

What could I do instead of the proc upload?

It seems that the possibility is not installed because I get the following error:

ERROR: Procedure UPLOAD not found.

[HenryKobus]

You may use a datastep or proc datasets / proc copy instead:

data LIB_SQL.WXGC21K;
    set backup.WXGC21K ;
run;

[Kurt_Bremser]

3 hours of real time vs. 7 minutes of CPU time points to bandwidth issues between the SAS and DB server.

And 7 minutes vs. 15 seconds also means that the whole setup of the connection is inefficient.

Get in touch with your SAS and DB admins, and study the documentation for your specific database connection (ODBC).

Also see if you have ACCESS to SQL server licensed, it provides a more direct path (SAS - DB client - server instead of SAS - ODBC OS component - ODBC driver - server).

[mariopellegrini]

Thank you.

But assuming PROC UPLOAD worked, could it greatly improve data transfer times from sas to sql? ie i mean doing a dry proc upload of a table from sas to sql

Another question: can there not be a "SQL Pass-Through" code that makes data transfer efficient?

[Kurt_Bremser]

PROC UPLOAD is part of SAS/CONNECT, which is used to communicate between two SAS processes. It is not used to connect to a database server.

[PaigeMiller]

---

@mariopellegrini wrote:

Good morning. A proc append in a SQL data library takes a very long time to append data. However, the same data on a basic sas library takes a few seconds. I report the 2 example logs.

I'm asking for suggestions on which method is better to hang data on a Sql Server data library from sas:

---

Better in what sense? If the criterion for "better" is the amount of time it takes, you have already found the answer.

[mariopellegrini]

Yes, but I need to bring data to SqlServer

[SASKiwi · Accepted solution]

In my experience, when appending SAS data to SQL Server tables you need to experiment with the INSERTBUFF data connection option. Changing this option can make a huge difference in table load times as the default value is not optimal. Try something like this, then experiment by increasing or reducing the value to see the impact:

LIBNAME EDWHDSST SQLSVR Datasrc=EDWHDSST SCHEMA=DBO AUTHDOMAIN="SQLSRVAuth"  insertbuff = 10000;

Also to connect to SQL Server in PROC SQL you can use your LIBNAME: 

proc sql;
  connect using EDWHDSST;
  create table MyTable as
  select * from connection to EDWHDSST
  (SELECT * FROM TABSQL) ;
  disconnect from EDWHDSST;
quit;

[mariopellegrini]

Thank you very much! Thanks to this option the elaboration went to 3'39"!

Is 10,000 usually what works best? Do you still advise me to try other values? Changing by how much?

[SASKiwi]

@mariopellegrini - I don't think there is an ideal number for everyone. I usually try a few different values, like 1,000, 2,000, 5,000, 10,000, 15,000, 20,000 to see which gives the best performance. We currently use 10,000 for most of our loads and that works well. There is no point using a larger number than the number of rows you are appending though.