BookmarkSubscribeRSS Feed
HeidiDT
Quartz | Level 8

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?

8 REPLIES 8
Kurt_Bremser
Super User

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.

HeidiDT
Quartz | Level 8

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?

Kurt_Bremser
Super User

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.

HeidiDT
Quartz | Level 8

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. 

Kurt_Bremser
Super User

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.

s_lassen
Meteorite | Level 14

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.

HeidiDT
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1864 views
  • 0 likes
  • 3 in conversation