10-25-2013 12:14 PM
Hello everyone. I Have processes that move data from our sql server database into my local machine (to allow lots of table manipulation), and also re-insert data into Sql itself.
The issue is my sql server database is located on the other side of the United States (literally) from my computer and I run a local sas machine license. I only have base sas to do this and getting sas on a server is out of the question.
So based upon this, does anyone know a more efficient way to move a table from a sql server database on the other side of the country to a local machine?
I have been told that proc download is the fastest way to do this, but I do not know if you can use proc download a sql server database table, and when I tried it did not seem to work (although maybe there is an option I am missing?).
To be clear, I am trying to find the most efficient way to do the following. I KNOW my network is the reason this code takes a long time, as the cpu time for sas is ~30 seconds while the clock time is > 2 hours. Also, if I do this code After copying the table from the server (aka If I copy it to work, and then re-create the table in the same manner pulling from the work table) it only takes about a minute. So i'm basically losing a 120 fold process efficiency by going over my network!
libname Myconne odbc dsn='mydsn' schema=dbo bulkload=yes dbmax_text=32000;
create table Mytable as
select * from mycone.Databasetable;
Thanks for your help!
10-25-2013 01:46 PM
You are using the ODBC access interface. That is you only connection to the SQL server by a local SAS process (you local machine).
By that Proc download SAs/connect is out of scope as they are focussed to a server-based SAS you do not have.
You pointing to the performance is almost complete, as you have:
- ODBC driver (local)
- SQL Server processing
Perhaps you could improve some things with parameters (buffsize / locking) using SAS/ACCESS to SQL-server options and doing copy once.
AS you ahve found the bulk-load option you are already aware of that. Try to calcualted the size of the dataset en your connection speed. When the network line usage will be up to 50% you are limited by that.