I still see a high amount of MS SQL wait types of ASYNC_NETWORK_IO (SAS reads data slower than SQL sends it), it would be great to find a way to reduce this wait type
This isn't necessarily SAS's fault. If your SQL Server (SS) is highly spec'd, but either 1) your network between SS and SAS is slow, 2) your SAS server is lowly spec'd, 3) your driver is inefficient (i.e. Microsoft's ODBC driver), 4) other reasons?, then you will see these wait types.
https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/
It primarily happens when SAS runs data "single threaded" instead of parallel processing
Have a look at the dbsliceparm and dbslice options:
https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p0qs980zd6e2ain0zb0903igkfje.htm&docset...
https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1etmj5mp89b8un13on2um4ivhav.htm&docset...
Here is an excerpt from one of my programs:
options dbsliceparm=(ALL,4);
* dbslice: <table integer column> % (T-SQL mod operator) <number of threads> = 0 - <number of threads - 1> ;
* if you use rldx_archive_key, add an additional slice to account for NULL rldx_archive_keys ;
* the sum of the row counts in each slice MUST equal the total row count of the source table! ;
set
tmp.rldx_pub_master (
dbslice=(
"rldx_archive_key % 4 = 0"
"rldx_archive_key % 4 = 1"
"rldx_archive_key % 4 = 2"
"rldx_archive_key % 4 = 3"
"rldx_archive_key % 4 IS NULL"
)
)
tmp.rldx_pvt_master (
dbslice=(
"rldx_archive_key % 4 = 0"
"rldx_archive_key % 4 = 1"
"rldx_archive_key % 4 = 2"
"rldx_archive_key % 4 = 3"
"rldx_archive_key % 4 IS NULL"
)
)
end=eof;
Using this approach, I got much better performance than without these options.
The best candidate for the dbslice column is a NOT NULL column with even distribution across the table, such as an IDENTITY column. SAS will attempt to pick a suitable column and algorithm (usually (always?) MOD) to slice the table, but I like to explicitly specify the dbslice conditions.
it only occurs when SAS communicates to sql.
Are you sure about that? Have you analyzed this wait type against all your other applications that hit your SQL Server...or just SAS? What about your .Net applications that pull GB's of data from SS and process the results on a "slow local drive"? And those applications would need to be pulling the same volume of data as you are likely pulling from SAS. And have the same network speed back to your server. Etc, etc.
Often users are pulling data down to their slower local drives into sas,
Not SAS's fault then. Improve the I/O to the "slower local drives" to the same standard as your SS disk I/O.
A few tips that I have passed on to others:
a. if your joining a table that exists in SAS to SQL, that table doesnt exist in SQL so SAS will "select the columns needed and pull them back to sas then join", so they could add the table to sql if needed (and index it) or index their table in sas
Agreed, in general I find homogeneous joins to be faster. IOW:
If you have a large table in SS, and a small dataset in SAS, upload the SAS dataset to SS, join using explicit passthrough, then download the (hopefully smaller) results to SAS (if needed - you might continue downstream processing with the data already on SS, which is what we often do).
If you have a large dataset in SAS, and a small table in SS, AND if your SAS server has similar capabilities as your SS (768GB of ram and 24 cores (xeon), it can read/write at 2000MBs+), then download the small SS table to SAS and join there.
In my environment, we have a powerful SS machine, a puny SAS machine (sigh), and average network speed between the two (not fast, not slow), so we do as much processing on the SS machine as possible by uploading data to SS and using explicit passthrough. We optimize complex queries in SSMS before cut-and-pasting to SAS.
b. remove "SAS" code that doesnt exist in sql (macro variables, certain functions etc)
Incorrect advice. SAS macros and macro variables get resolved in a pre-compile phase. A bit analogous to the pre-processor in C programming. Macro and macro variables are, in general - ignoring data step interfaces such as symputx and symget - just a code generation step, BEFORE the SAS compiler or SAS procedure executes. There is nothing wrong with including SAS macros or macro variables in SQL code; they get resolved even in explicit passthrough before SAS sends the results to SS. The end result is the equivalent of you typing in the resolved code from the macro or macro variable.
Yes, if there is some SAS specific function that cannot be sent to SS, then SAS may have to download the entire SS table, then filter within SAS. But there is usually a way of optimizing this esp. via native T-SQL and explicit passthrough. And you would face the same issues in a .Net program, Python program, Excel workbook, etc., that was hitting your SS. If your data is in SS, filter it first on SS, then apply your application logic/processing.
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
