11-02-2017 04:02 AM
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:
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;
BUT it takes 2 hours! Is there an alternative method that would reduce the runtime?
11-02-2017 04:12 AM
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.
11-02-2017 04:15 AM
Also consider setting a libname for your SQL Server database and use that. Hints for tuning ACCESS libraries can be found here:
11-02-2017 06:46 AM
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?
11-02-2017 07:14 AM
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.
11-02-2017 07:21 AM
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.
11-02-2017 07:29 AM
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.
11-03-2017 04:45 AM
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.