Usage Note 33417: Poor performance occurs when you join two tables from different databases that are on the same server
Poor performance occurs when you join two database tables. As an example, suppose you submit the following two LIBNAME statements, each of which are to the same data source. However, each statement’s libref specifies a different database:
libname one oledb init_string="Provider=SQLOLEDB.1;
integrated security=SSPI; persist security info=true;
initial catalog=DBONE; data source=SERVER1;" schema=DBO;
libname two oledb init_string="Provider=SQLOLEDB.1;
integrated security=SSPI; persist security info=true;
initial catalog=DBTWO; data source=SERVER1;" schema=DBO;
When you join tables from each of the libref, poor performance occurs. If you submit the SASTRACE= option, the following error message appears:
ERROR: This SQL statement will not be passed to the DBMS for
processing because it involves a join across librefs with
different connection properties
When you submit the two librefs and they specify two different databases on the same DBMS server, the implicit Pass-Through facility is turned off. Explicit Pass-Through must be used to join tables from different databases on the same server.
Joins can be passed when two librefs are submitted to the same DBMS server and database if the schemas are different.
Operating System and Release Information
SAS System | SAS/ACCESS Interface to OLE DB | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 SP4 | |
Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 SP4 | |
Microsoft® Windows® for x64 | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Server | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Professional | 9.1 TS1M3 SP4 | |
Microsoft Windows NT Workstation | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 SP4 | |
Microsoft Windows XP Professional | 9.1 TS1M3 SP4 | |
Windows Vista | 9.1 TS1M3 SP4 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Date Modified: | 2008-11-11 16:34:50 |
Date Created: | 2008-09-25 15:20:30 |