Threaded Reads Performance Impact of Threaded Reads 45
You will receive the following warning:
WARNING: Unable to find a partition column for use w/ MOD()
Threading disabled due to unrecoverable error.
YOEGET: Threading is disabled due to an error.
Application reverts to non-threading I/O’s.
If the SAS job contains any options that are invalid when the engine tries to do
threading you will also receive a warning.
data work.locemp:
set trlib.MYEMPS (OBS=10);
where EMPNUM<=30;
run;
This will produce:
YOEINFO: Threading is disable due to the ORDER BY clause or the FIROBS/OBS option.
To produce timing information, add the option
debug=dbms_timers;:
options sastrace=’,,,d’ debug=dbms_timers;
data work.locemp;
settrlib.MYEMPS;
where EMPNUM<=30
run;
This will produce the following:
XOTGET: Thread 0 fetched 15 rows
XOTGET: DBMS Read Total Time: 290mS
XOTGET: DBMS Read User CPU: 30mS
XOTGET: DBMS Read System CPU: 110mS
Enter yoetcls()
XOTGET: Thread 1 fetched 15 rows
XOTGET: DBMS Read Total Time: 380mS
XOTGET: DBMS Read User CPU: 40mS
XOTGET: DBMS Read System CPU: 140mS
Performance Impact of Threaded Reads
Threaded reads only increases performance when the DBMS result set is large.
Performance is optimal when the partitions are similar in size. Threaded reads should
reduce the elapsed time of your SAS step, but in unusual cases it can slow down the
SAS step. It generally increases the workload on your DBMS.
SAS automatically tries to autopartition table references in threaded applications. To
determine whether autopartitioning is occurring and to assess its performance,
complete the following tasks:
Turn on SAS tracing to see if SAS is autopartitioning and to view the SQL
associated with each thread.
Know your DBMS algorithm for autopartitioning.
Turn threaded reads on and off, and compare the elapsed times.
To optimally tune threaded reads, follow these guidelines:
Use it only when pulling large result sets into SAS from the DBMS.
Use DBSLICE= to partition if SAS autopartitioning does not occur.
