topic Re: Reading SQL Server Tables into SAS in SAS Programming

Reading SQL Server Tables into SAS

HeidiDT — Thu, 02 Nov 2017 08:02:30 GMT

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?

Re: Reading SQL Server Tables into SAS

Kurt_Bremser — Thu, 02 Nov 2017 08:12:25 GMT

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.

Re: Reading SQL Server Tables into SAS

Kurt_Bremser — Thu, 02 Nov 2017 08:15:43 GMT

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

Re: Reading SQL Server Tables into SAS

HeidiDT — Thu, 02 Nov 2017 10:46:24 GMT

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?

Re: Reading SQL Server Tables into SAS

Kurt_Bremser — Thu, 02 Nov 2017 11:14:41 GMT

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.

Re: Reading SQL Server Tables into SAS

HeidiDT — Thu, 02 Nov 2017 11:21:52 GMT

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.

Re: Reading SQL Server Tables into SAS

Kurt_Bremser — Thu, 02 Nov 2017 11:29:05 GMT

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.

Re: Reading SQL Server Tables into SAS

s_lassen — Fri, 03 Nov 2017 08:45:43 GMT

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.

Re: Reading SQL Server Tables into SAS

HeidiDT — Fri, 03 Nov 2017 08:50:15 GMT

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