Concern 1: how to reduce async_network_io (allow sas to read rows quicker so it can keep up with sql)
Concern 2: how to have SAS's code sent to the MS SQL server utilize parallel processing (instead of 1 core)
I have utilized various options and hints to encourage SAS to utilize parallel processing, however the code continues to utilize 1 cpu core (SQL is set to MAXDOP = 😎 and single threaded. The same queries ran in MS SQL directly run in 10% of the time than SAS.
Is there a hint or driver that would resolve this issue? I have observed the same issue with the "SQL driver", "Natural SQL Driver 11.0 and 10.0" and the latest "ODBC Driver 17 for SQL Server".
The select/create table statement should be pulling the data down from sql multi/parallel threaded, any assistance? Thanks a ton!
/* tried with and without option flags */
OPTIONS BUFNO=1000;
OPTIONS VBUFSIZE=1024K;
OPTIONS UBUFNO=20;
OPTIONS UBUFSIZE=1024K;
OPTIONS IBUFSIZE=32767;
OPTIONS IBUFNO=100;
OPTIONS BUFSIZE=64k;
OPTIONS THREADS CPUCOUNT=6;
OPTIONS THREADS=YES;
/* tried with and without libname options */
LIBNAME MYSCHEMA ODBC NOPROMPT =
"Driver=ODBC Driver 17 for SQL Server;
Server=MYSERVER;
BULKLOAD=YES;
READBUFF=32767;
DBOMMIT=32767;
INSERTBUFF=32767;
ROWSET_SIZE=32767;
Database=MYDATABASE;
DBSLICEPARM=all;
THREADS=yes;
CPUCOUNT=8;
Trusted_Connection=Yes;
"
Schema = MYSCHMEA;
/* example - however I am selecting about 30-50 columns
also tried proc sql THREADS; without noticing a change.
*/
proc sql;
Create table simple_Test as SELECT
a
,b
,c
,d
,e
,f
,g
FROM MySCHEMA.MYTABLE
; quit;
Installed sas plugins:
For Base SAS Software ...
Custom version information: 9.4_M6
Image version information: 9.04.01M6P110718
For SAS/STAT ...
Custom version information: 15.1
For SAS/GRAPH ...
Custom version information: 9.4_M6
For SAS/CONNECT ...
Custom version information: 9.4_M6
For High Performance Suite ...
Custom version information: 2.2_M7
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M6
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M6