#26 Improve stmt.executeOlapQuery Performance

[sherwinw]

Hi - I am using olap4j 0.9.8.344 to query a SQL Server cube. This olap4j statement, stmt.executeOlapQuery(mdxQuery), returned 30k rows in 40+ minutes. The same query returned in several seconds in SQL Server Client. Any idea how this can be improved? Thanks.

2011-05-24 19:58:03 UTC
I am guessing that the XMLA driver is parsing a massive SOAP response into an DOM tree in memory. The client is probably thrashing.
Massive result sets are not a case that we have optimized in the XMLA driver.
A solution could be to use an event-driven XML parser (such as SAX or StAX) to parse the result incrementally, and iterate over the axis using a cursor. (Forward only.)
This would be a significant new feature. Please log a feature request.
As a workaround, try giving your client more memory.
jhyde

[Sergio]

I've got also problems with massive data.

I'm using olap-1.0.0.445, and I tested with mondrian examples.

A query like this:
SELECT HIERARCHIZE ({ [Customers].[Name].MEMBERS }) * HIERARCHIZE ({ [Store].[Store Name].MEMBERS }) ON ROWS, { [Measures].[Customer Count]} ON COLUMNS FROM [Sales]'.

Causes a Java Heap Space in my client, started with 1gb for maximum heap.

In stacktrace can be seen clearly the produced error:

Caused by: java.lang.OutOfMemoryError: Java heap space
at org.apache.xerces.xni.XMLString.toString(Unknown Source)
at org.apache.xerces.parsers.AbstractDOMParser.characters(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanContent(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
at org.olap4j.driver.xmla.XmlaOlap4jUtil.parse(XmlaOlap4jUtil.java:71)
at org.olap4j.driver.xmla.XmlaOlap4jCellSet.populate(XmlaOlap4jCellSet.java:87)
at org.olap4j.driver.xmla.XmlaOlap4jStatement.executeOlapQuery(XmlaOlap4jStatement.java:332)

There a DOM parser instead a SAX. So, also I would like a change in XML parser, I think a SAX is the better option.

Is this a realistic enhancement for olap4j?

Thanks.