Performance Impact of Threaded Reads

Threaded reads only increase performance when the DBMS result set is large. Performance is optimal when the partitions are similar in size. Using threaded reads should reduce the elapsed time of your SAS step, but unusual cases can slow the SAS step. They generally increase the workload on your DBMS.

For example, threaded reads for DB2 under z/OS involve a tradeoff, generally reducing job elapsed time but increasing DB2 workload and CPU usage. See the auto partitioning documentation for DB2 under z/OS for details.

SAS automatically tries to autopartition table references for SAS in threaded applications. To determine whether autopartitioning is occurring and to assess its performance, complete these tasks:

• Turn on SAS tracing to see whether 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.

Follow these guidelines to ensure optimal tuning of threaded reads:

• Use it only when pulling large result sets into SAS from the DBMS.

• Use DBSLICE= to partition if SAS autopartitioning does not occur.

• Override autopartitioning with DBSLICE= if you can manually provide substantially better partitioning. The best partitioning equally distributes the result set across the threads.

• Consult the DBMS-specific section of this documentation for information and tips concerning your specific DBMS.

Threaded reads are most effective on new, faster computer hardware running SAS, and with a powerful parallel edition of the DBMS. For example, if SAS runs on a fast uniprocessor or on a multiprocessor machine and your DBMS runs on a high-end SMP server, you will receive substantial performance gains. However you receive minimal gains or even performance degradation when running SAS on an old desktop model with a nonparallel DBMS edition running on old hardware.

Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.