topic How to save time to extract data from SQL Server. in SAS Data Management

How to save time to extract data from SQL Server.

jc259star — Mon, 27 Jul 2015 17:22:01 GMT

Hi ,

I want to extract data from SQL Server. I successfully connect my computer with SQL Server by ODBC .

Then I run proc sql to extract data from one table in SQL Server database:P_2013. The SAS log showed me that it took ~40 min for extracting the data.

To save time, I run the same SAS program in three computers simultaneously to extract data from other tables of the same database:P_2013.

The SAS log surprisingly showed that it took ~130 min for extracting the data, about three times as one computer . No time was saved at all!

Would you please suggest me how to save time to extract data from the tables of the same database in SQL Server ?

Re: How to save time to extract data from SQL Server.

LinusH — Tue, 28 Jul 2015 08:38:18 GMT

Sounds like you have a constraint outside SAS. To verify use

options fullstimer;

You have no filters? If not, I think that the best way forward is going to your dba/nw/Windows server admins. They should be able to help you find the bottlenecks.

Re: How to save time to extract data from SQL Server.

jc259star — Tue, 28 Jul 2015 21:12:42 GMT

Hi Linus,

Thank you very much for your advice. I will go to our Server Admins for more information.

Re: How to save time to extract data from SQL Server.

TomKari — Tue, 28 Jul 2015 22:00:30 GMT

Very likely your bottleneck is your database server, or possibly your networking environment. If your server is running as hard as it can to meet your one request, sending it three requests will stretch out the time for each request. Likewise, if one request fills your network "pipe", sending three times the data through the pipe will take three times as long.

Your database and network administrators should be able to help you determine how heavily loaded the infrastructure is.

Tom

Re: How to save time to extract data from SQL Server.

morgalr — Tue, 28 Jul 2015 23:58:11 GMT

jc,

I can verify what Tom Kari said, being an Oracle Db I see this happen a lot. When the network pipe is full, the network pipe is full, you just have to wait due to bandwidth limitations. If your server is maxing out trying to pull your data, you cannot get more down the pipe, even if it is open, than the server will push.

As stated by others, check with your admins to see if this is the case, but here is an idea to consider: "Can any of your data be considered static?" If it can be, then you should consider storing static portions locally; thus, not having to pull at least part of the data through the network pipe or server. When you need a fresh copy of the data, then delete the dynamic content and only pull data that is not considered static.

You'll need to check with your IT/Security managers to see if storing the data locally is an acceptable option for your location.

Re: How to save time to extract data from SQL Server.

jc259star — Wed, 29 Jul 2015 18:24:17 GMT

Hi Tom,

Thank you very much for the information.

Jun

Re: How to save time to extract data from SQL Server.

jc259star — Wed, 29 Jul 2015 18:25:47 GMT

Hi Morgalr,

Thank you for the suggestion.

Jun