Static cursors will create a temporary table behind the scenes whenever one would not have been created by the relational engine (for more information about temporary tables, see SQL Engine Reference). This will be the case for both command-based and navigational tables. When bandwidth is not a significant consideration, dynamic cursors can provide higher performance, since they do not always involve temporary tables. However, in low bandwidth scenarios, round-trips can be too expensive to justify dynamic cursors; in this case RDS is often a good solution. The drawback to RDS is that Microsoft has implemented it as a command-based only solution, which means that index functionality (using Seek) is unavailable. Performance can be maintained regardless of deployment by implementing an abstraction layer that works identically on RDS-based and local recordsets. The nature of this abstraction would depend on the needs of the application and would likely take the form of a runtime business object.
Turning off the Automatic Transaction Enlistment will not instantiate the ITransactionJoin interface on the session and will also keep the provider from looking for MTS objects.