Poor SQL Performance on large volumes

[srapp88]

Hi

We are experiencing very slow data writes from SQL into SAS.

Platform: 

SQL Server 2016 in Azure (IAAS)

SAS Environment on RHEL 7

SAS 9.4

Access SQL Server using ODBC

I have put a table in SQL that has 2.1million rows~ with 537 columns

So i assing the 2 Librarys in EG one that is on SQL server once on NFS Shared storage and run the following:

options FULLSTIMER;
data PERFDTA2.BS1_SQL2DATA2_1DAYDATA2_4;
set SQLTEST.BS1_1DayData_PERFTEST (obs=100000);
run;

This writes 100k rows in 21seconds

If i run the same command without the obs:

options FULLSTIMER;
data PERFDTA2.BS1_SQL2DATA2_1DAYDATA2_4;
set ALPSNDBX.BS1_1DayData_PERFTEST;
run;

So it selects the entire table in SQL 2.1Million rows~ it then takes 1hr 45minutes.

Can anyone advise why the scale up?

I even killed the job earlier inadvertantly and it had written 400k in 4 minutes. I don't understnad why when the voilume goes up so much the time is insanely long.

Any additionaly information required please let me know.

[Kurt_Bremser]

You have one network connection to the SQL server and one to NFS, and use both concurrently. As soon as the initial buffers are used, the network contention kills you.

Move the SAS library to internal storage or the SAN.

100k rows read in 21 seconds would be OK if this was 1990. With a 1 Gbit network and internal disks, it should be finished in a second for observation sizes < 1000 bytes.

[srapp88]

Simply put i run this exact same process on an old SAS9.2 windows installation with no issues, so i know it can be done fine but there must be config issue somewhere either architecturally or within SAS or potentially hardware but the specs of hardware for what i am testing are way above what is required. 

So between servers are 10Gig network connections, network through put will not be the issue. We have run performance metrics and can see we are not saturating the network link either between SQL <> SAS Server <> NFS Server

Simple file transfes are fine. All disks use are on SSD 

I am fully aware 21secs is not good enough but if i can get the process to run in 10minutes down from 2 hours i can work on it further from there.

[Kurt_Bremser]

Network bandwidth is not really an issue if you cause packet collisions.

Do a ping between servers while your process is running.

And do a crosscheck with internal disks instead of the NFS mounts.

[Kurt_Bremser]

And Windows means no NFS; NFS clients on Windows are extremely rare.