SSAS查询在SSMS与Java MDX（Olap4j）中的性能差异排查

Hey there! I totally get your frustration—seeing SSMS return results instantly while your Java service takes 30+ seconds with the same MDX is confusing, especially when you’re coming from a Java background and new to SSAS. Let’s break down why this happens and walk through actionable fixes to get your service up to speed.

First, Why SSMS is Faster #

SSMS uses the ADOMD.NET client, which talks to SSAS via a native binary protocol (Tabular Data Stream over TCP/IP) instead of XMLA over HTTP like olap4j. This native protocol is way more efficient: it’s compact, uses binary serialization, and often has built-in compression enabled by default.

Additionally, SSMS’s browse interface might not be running the exact MDX you copied—it could be using optimized internal APIs to fetch data incrementally or filter empty cells client-side before rendering. The generated MDX is a verbose representation of what the UI is doing, not the optimized version SSMS actually uses under the hood.

Actionable Optimization Steps #

1. Enable HTTP Compression in Olap4j #

Your Wireshark data shows tons of verbose XML being sent over the network—compressing this will drastically reduce the data size and speed up transfers. Olap4j lets you add HTTP headers to request compressed responses:

import java.util.Properties;
import java.sql.Connection;
import java.sql.DriverManager;
import org.olap4j.OlapConnection;
import org.olap4j.OlapStatement;
import org.olap4j.CellSet;

Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");

Properties props = new Properties();
props.put("user", "your_username");
props.put("password", "your_password");
// Request gzip/deflate compression from the server
props.put("http.headers", "Accept-Encoding: gzip, deflate");

Connection connection = DriverManager.getConnection(cubeUrl, props);
OlapConnection olapConnection = connection.unwrap(OlapConnection.class);
olapConnection.setCatalog(catalog);
OlapStatement statement = olapConnection.createStatement();

CellSet cellSet = statement.executeOlapQuery("<<Your MDX Query>>");

Make sure your IIS/SSAS endpoint is configured to support compression (we’ll cover that next).

2. Enable Dynamic Compression on IIS/SSAS #

If your SSAS XMLA endpoint is exposed through IIS, you need to enable dynamic compression (since XMLA responses are dynamically generated):

• Open IIS Manager, navigate to your SSAS site
• Go to Compression under the IIS section
• Check "Enable dynamic content compression"
• Ensure application/xml is included in the list of compressed MIME types

This will let IIS compress the XML responses before sending them to your Java service, matching the compression SSAS uses with ADOMD.NET.

3. Optimize Your MDX Query #

Your current MDX generates a massive Cartesian product of all measure members, including tons of empty cells. Even though you enabled "Show Empty Cells" in SSMS, SSMS might be filtering these empty cells client-side to speed up rendering. Add NON EMPTY to your rows clause to cut down on the data sent over the wire:

SELECT { } ON COLUMNS,
NON EMPTY { ( 
  [Main].[Measure01].[Measure01].ALLMEMBERS * 
  [Main].[Measure02].[Measure02].ALLMEMBERS * 
  [Main].[Measure03].[Measure03].ALLMEMBERS * 
  [Main].[Measure04].[Measure04].ALLMEMBERS 
) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( { [Main].[Measure01].&[1] } ) ON COLUMNS FROM [Model]) 
CELL PROPERTIES VALUE

This will eliminate empty cell combinations from the response, drastically reducing the number of XML elements transmitted.

4. Reduce Unnecessary Properties #

Take a look at the DIMENSION PROPERTIES and CELL PROPERTIES clauses in your MDX. If you don’t need MEMBER_CAPTION (or other properties), remove them to slim down the XML response. For example, if you only need MEMBER_UNIQUE_NAME, adjust the clause to:

DIMENSION PROPERTIES MEMBER_UNIQUE_NAME ON ROWS

Every extra property adds more XML nodes, so trimming these can save significant bandwidth.

5. Try Incremental Fetching with Olap4j #

Olap4j supports incremental fetching, which lets you retrieve data in batches instead of all at once. This can reduce memory pressure on your Java service and make the transfer feel faster (even if total time is similar, it avoids waiting for the entire dataset):

// Use INCREMENTAL fetch mode to get data in chunks
CellSet cellSet = statement.executeOlapQuery(
  "<<Your MDX Query>>", 
  org.olap4j.CellSetFetchMode.INCREMENTAL
);

6. Use a Native SSAS Driver for Java (If Possible) #

If your Java service runs on Windows or can access SSAS’s native TCP port (default 2383), consider using a driver that supports SSAS’s native protocol instead of XMLA. The olap4j-adomdnet driver wraps ADOMD.NET and can give you performance closer to SSMS. Note that this requires ADOMD.NET installed on the server, but it’s a game-changer for speed.

Final Notes #

The biggest wins will likely come from enabling compression (both client and server-side) and optimizing your MDX to reduce empty cells. SSMS’s speed advantage comes from its native protocol and client-side optimizations, but with these tweaks, your Java service should get very close to that performance.

内容的提问来源于stack exchange，提问作者Matt