As a MS SQL DB admin I often find SAS code running inefficiently on the server which will generate "network I/O sync" wait times (degraded performance), run serial/single threaded (not parallel) and the queries from SAS are rewritten poorly in SQL or inefficiently. This occurs primarily when any custom SAS logic is used (macros) and the same behavior occurs with PROCSQL and data steps. This is a simple example.
Example a PROC SQL statement:
SELECT
A,B , SUM(PAID) AS PAID
FROM X
WHERE A between 2000 and 3000
GROUP BY
A,B
What it should do is rewrite a similar query in SQL. However when you look at SQL this is how the thread will look like:
SELECT "A", "B", "PAID"
FROM X
WHERE ( ( "A" between "2000" and "3000"))
- it is not performing a GROUP statement. So it ends up pulling ALL records serial/single threaded over the network (300mil rows) to the person's SAS workspace/PC THEN it performs the group by statement in house. The table is columnstore indexed and can be summarized in SQL in 5 seconds vs hours in SAS (since SAS wants to recreate EACH ROW on a local pc, over a network, single threaded...)
BULK INSERTS perform even worse with even more bizarre code observed in SQL .
LIBNAME TEST ODBC NOPROMPT =
"Driver=SQL Server Native Client 11.0;
Server=myserver;
Database=myDB;
dbsliceparm=all;
threads=yes CPUCOUNT=8;
READBUFF=32767;
DBOMMIT=32767;
INSERTBUFF=32767;
ROWSET_SIZE=32767;
Trusted_Connection=Yes;"
Schema = TEST ;
