Reading SQL Server Tables into SAS

Reply
Contributor
Posts: 24

Reading SQL Server Tables into SAS

Good day

I use SQL passthru to create a temp table (the result of a number of temp tables and then finally joining to create the final temp table) in SQL Server. The resulting table has 6 columns and around 8.7m rows. I need to copy that into a sAS dataset, and I opted to use this method:

 

proc sql;
connect to SQLSVR (CONNECTION=GLOBAL DATAsrc=&DATASRC_EXT. USER=&SM_USER. PASSWORD=&SM_PASS.);
create table final_location as select * from connection to sqlsvr (select * from #Final_Location);
disconnect from sqlsvr;
quit;

 

BUT it takes 2 hours! Is there an alternative method that would reduce the runtime?

Super User
Posts: 9,924

Re: Reading SQL Server Tables into SAS

What is the physical size of the resulting SAS table? From that, you can deduce the overall transfer rate. Check if any other data transfer to/from the SQL server is equally slow; if yes, the network connection is your problem.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,924

Re: Reading SQL Server Tables into SAS

Also consider setting a libname for your SQL Server database and use that. Hints for tuning ACCESS libraries can be found here:

http://support.sas.com/documentation/cdl/en/bidsag/61236/HTML/default/viewer.htm#a002668484.htm

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 24

Re: Reading SQL Server Tables into SAS

Posted in reply to KurtBremser

Thanks! Adding READBUFF= reduced the time to 1 1/2 hours. But in another SAS job I also copy data from SQL server to a SAS dataset, same amount of rows but WAY more columns, and that copy only takes around 15 minutes. Could the fact that the first table is a temporary table make a difference?

Super User
Posts: 9,924

Re: Reading SQL Server Tables into SAS

I wouldn't look so much at the number of columns, but at the record/observation size. One long string variable easily outweighs 10 or 20 numbers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 24

Re: Reading SQL Server Tables into SAS

Posted in reply to KurtBremser

The table that takes longer to copy has only 1 character variable (19 charactes) and 5 numeric variables. The one that copies in 15 minutes has 217 variables, of which 26 are character variables. 

Super User
Posts: 9,924

Re: Reading SQL Server Tables into SAS

Then next I'd run the query in SQL Server only (from your DB client on the SAS machine), to see if the bad performance happens there. And consult with your DBA's. It might be that they mutter "oh, my", adjust some settings, and Bob's your uncle.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 254

Re: Reading SQL Server Tables into SAS

One reason why the other query (from a larger, permanent table) goes faster can be that the permanent table is partitioned. In that case, SAS SQL can extract each partition in parallel, making the transfer go faster.

Contributor
Posts: 24

Re: Reading SQL Server Tables into SAS

I just remembered that the permanent "table" is in actual fact a view!

Ask a Question
Discussion stats
  • 8 replies
  • 191 views
  • 0 likes
  • 3 in conversation