Problem Note 50733: An SQL query containing date arithmetic might not be pushed to the underlying database, leading to poor performance
 |  |  |  |  |
Instead of pushing the join operation to the database, data from the tables are pulled into SAS®, where the join is applied.
Example:
proc sql;
select case
when a.date1 - a.date2 >365
then 1
else 0
end as theval
from dblib.table1 a, dblib.table2 b
where a.key=b.key;
quit;
Click the Hot Fix tab in this note to access the hot fix for this issue.
With the hot fix, the join of the tables is pushed to the database, thereby improving performance. The date arithmetic part of the query is still being evaluated in SAS. This is done to ensure data integrity, since date arithmetic in databases might yield different results than SAS is producing.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M3 | 9.2 TS2M3 |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft® Windows® for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2008 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2008 R2 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Microsoft Windows XP Professional | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Enterprise 32 bit | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Enterprise x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Home Premium 32 bit | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Home Premium x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Professional 32 bit | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Professional x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Ultimate 32 bit | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows 7 Ultimate x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows Vista | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Windows Vista for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| 64-bit Enabled AIX | 9.2 TS2M3 | 9.2 TS2M3 | ||
| 64-bit Enabled HP-UX | 9.2 TS2M3 | 9.2 TS2M3 | ||
| 64-bit Enabled Solaris | 9.2 TS2M3 | 9.2 TS2M3 | ||
| HP-UX IPF | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Linux | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Linux for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
| OpenVMS on HP Integrity | 9.2 TS2M3 | 9.2 TS2M3 | ||
| Solaris for x64 | 9.2 TS2M3 | 9.2 TS2M3 | ||
A fix for this issue for Base SAS 9.21_M3 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/B25.html#50733A fix for this issue for Base SAS 9.3_M2 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/I22.html#50733
An SQL query containing date arithmetic might not be pushed to the underlying database, leading to poor performance.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2013-08-20 11:18:41 |
| Date Created: | 2013-08-14 08:41:54 |