Problem Note 44267: MDX ORDER function might generate a large number of subqueries
 |  |  |  |  |
For MDX queries that use the MDX ORDER function with the ASC or DESC parameter, an unexpectedly large number of subqueries might be generated to satisfy the request. When ASC or DESC is specified, the ORDER function first arranges the members according to their position in the hierarchy, and then orders each level. This causes the OLAP Server to generate one subquery to obtain all the data, followed by individual subqueries to retrieve values cell by cell. If you have a large result set, this process could result in thousands of subqueries that might greatly impact the response time.
Following is a simple query:
select measures.quantitysum on 0, order(product.product_category.members ,measures.quantitysum,asc) on 1 from Starcube
This query generates the following subqueries to satisfy the result set:
select distinct PRODUCT_LINE, PRODUCT_CATEGORY, sum(QUANTITYSUM)
from TOLAPSIO.CPLPC
group by 001, 002
having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
from TOLAPSIO.YRP
where ((PRODUCT_LINE eq 'Children'))
group by 001
having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
from TOLAPSIO.YRP
where ((PRODUCT_LINE eq 'Clothes & Shoes'))
group by 001
having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
from TOLAPSIO.YRP
where ((PRODUCT_LINE eq 'Outdoors'))
group by 001
having count(*) gt 0
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
from TOLAPSIO.YRP
where ((PRODUCT_LINE eq 'Sports'))
group by 001
having count(*) gt 0
select distinct PRODUCT_LINE, PRODUCT_CATEGORY, sum(QUANTITYSUM)
from TOLAPSIO.CPLPC
group by 001, 002
having count(*) gt 0
Click the Hot Fix tab in this note to access the hot fix for this issue. Once the hot fix is applied, the above four central subqueries will be replaced with the following:
select distinct PRODUCT_LINE, sum(QUANTITYSUM)
from TOLAPSIO.YRP
group by 001
having count(*) gt 0
You can also circumvent this behavior by substituting BASC and BDESC, respectively, for ASC and BDESC. The BASC and BDESC parameters return the members in the result set without regard to the hierarchy.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS OLAP Server | Solaris for x64 | 9.2 TS2M0 | 9.3 TS1M0 |
| OpenVMS on HP Integrity | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Linux for x64 | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Linux | 9.2 TS2M0 | 9.3 TS1M0 | ||
| HP-UX IPF | 9.2 TS2M0 | 9.3 TS1M0 | ||
| 64-bit Enabled Solaris | 9.2 TS2M0 | 9.3 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.2 TS2M0 | 9.3 TS1M0 | ||
| 64-bit Enabled AIX | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Windows Vista for x64 | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Windows Vista | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows XP Professional | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | 9.3 TS1M0 | ||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | 9.3 TS1M0 | ||
| z/OS | 9.2 TS2M0 | 9.3 TS1M0 | ||