SAS 9.3 connect to SQL Server - Updates to large table hangs

MadhuriDara — Fri, 17 Jul 2020 05:45:29 GMT

Hi, I am using SAS 9.3 to connect to SQL Server 2017 (AWS RDS) . I run an update statement like this: libname SQL ODBC DSN=SQL_ODBC UID=UID PASSWORD=PWD DIRECT_EXE=DELETE; PROC SQL; UPDATE SQL.TAB100 SET KEY = 1 WHERE KEY IN (SELECT KEY FROM SQL.TAB200); QUIT; TAB100 has 5.3 million rows and TAB200 has 2 rows. This SAS statement just hangs for 2-3 hrs. When the update stmt is run on the SQL Server database it takes 2 sec. I have tired creating DSN with both these dlls: MSODBCSQL17.DLL and SQLNCLI11.DLL. The SAS statement hangs with both the ODBC DSNs. Could you please share your thoughts on how it could be improved. Thanks Much, Madhuri Dara

Re: SAS 9.3 connect to SQL Server - Updates to large table hangs

ChrisNZ — Fri, 17 Jul 2020 23:47:05 GMT

There is a wonderful thing called formatting, which is linked to another invention called paragraphs.

You should try to look into that. I hear it helps written communication.

Regarding your question, you should use explicit SQL pass-through.

The syntax is something like this:

proc sql;
  connect using SQL;
  execute by SQL (
    update TAB100 set KEY = 1 where KEY in (select KEY from TAB200)
  );
quit;

The way you wrote your query, the whole tables are sent to SAS and then back.

topic SAS 9.3 connect to SQL Server - Updates to large table hangs in SAS Programming

SAS 9.3 connect to SQL Server - Updates to large table hangs

Re: SAS 9.3 connect to SQL Server - Updates to large table hangs