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?
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.
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
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?
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.
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.
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.
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.
I just remembered that the permanent "table" is in actual fact a view!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.