Re: Bad performance for MDX query over XMLA

• Previous message: Akshai Mirchandani [MS]: "Re: Bad performance for MDX query over XMLA"
• In reply to: Akshai Mirchandani [MS]: "Re: Bad performance for MDX query over XMLA"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 21 Sep 2004 12:58:10 -0700

Note that in some cases, the network is the bottleneck, not your parser.
  I was troubleshooting a very similar problem with a GIGANTIC response
(50 MB of XMLA IIRC) to stress test my parser. No matter what I changed
the "parse" time was identical.

As it turned out, my parser was throttled by the network throughput.
When switched my code to download the response to a local file and then
parse from that local source, my (true) parser time shot up dramatically.

But, yes. SAX vs DOM can make a big difference if you are going to throw
away a lot of the response. Just make sure you stream to a file first so
that you remove your bandwidth from the performance equation.

-z

Akshai Mirchandani [MS] wrote:

> Yes, SAX vs DOM can be a big difference...
>
> Analysis Services 2005 has serveral new transport related features. It does
> of course still support text/xml for compliance with the XMLA spec. It also
> adds support for binding the XMLA protocol directly on TCP/IP (not a public
> API), compression, encryption, binary XML, etc. Lots of cool new features.
>
> Thanks,
> Akshai
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
>
> "ErwanB" <ebellec@yahoo.com> wrote in message
> news:eaf6f899.0409210118.32c016c6@posting.google.com...
>
>>Hello Akshai
>>
>>Thanks for your feedback.
>>
>>The query is already of the following type :
>>H1.members ON Axis(1),
>>H2.members ON Axis(2),
>>Therefore , there is not really much to dig into this direction.
>>
>>As you suggeste, I turned on the compression on IIS6 and it actually
>>improved the performance quite significantly ( query returned data in
>>10 min vs 60 min w/o compression). It's still 6 to 7 times slower
>>than PTS over HTTP... oh well.
>>
>>I will investigate the SAX vs DOM approach, as I can see that the
>>query itself is quite fast, but the return of the answser set down to
>>the client seems indeed to be very time consuming.
>>
>>Regarding your remark comparing binary vs TXT/XML, what will bring SQL
>>Server 2005 binary XMLA in this area ?
>>
>>Thanks for your help
>>
>>Regards
>>ErwanB
>>
>>"Akshai Mirchandani [MS]" <akshaim@online.microsoft.com> wrote in message
>
> news:<eBxC7f0nEHA.2764@TK2MSFTNGP11.phx.gbl>...
>
>>>Firstly, XMLA is not going to be as performant compared with a native
>
> binary
>
>>>protocol. Just the size of the response alone is going to be much larger
>>>aside from the cost of the serialization. This makes the most difference
>>>when you have a really large number of tuples in your axes and/or a
>
> large
>
>>>number of cells.
>>>
>>>Secondly, there are a couple of things you can do to reduce result
>
> sizes:
>
>>>- if your query is a large crossjoin, you could try to split it onto
>>>multiple axes. A result like this:
>>> H1.members * H2.members * ... ON ROWS
>>>would be much more expensive than
>>> H1.members ON Axis(1),
>>> H2.members ON Axis(2),
>>> ...
>>>because the total number of members would be much smaller. However, this
>>>causes other problems -- if you wanted to do NON EMPTY then it wouldn't
>>>return the same results as before.
>>>- you could also try to enable compression on IIS. We've got reports
>
> that
>
>>>turning on compression makes a fairly substantial difference.
>>>
>>>Another thing we noticed when we were doing our performance testing was
>
> that
>
>>>the code written on the client can make a huge difference when the
>
> results
>
>>>are large -- if you are simply loading the response into a DOM, then the
>>>performance is going to be really awful because building the DOM is very
>>>expensive. Your best bet is going to be to use a SAX parser (or
>
> equivalent)
>
>>>forward only parser. Or just dump the response into a file and work on
>
> it
>
>>>from there...
>>>
>>>Thanks,
>>>Akshai
>>>--
>>>This posting is provided "AS IS" with no warranties, and confers no
>
> rights
>
>>>Please do not send email directly to this alias. This alias is for
>
> newsgroup
>
>>>purposes only.
>>>
>>>"ErwanB" <ebellec@yahoo.com> wrote in message
>>>news:eaf6f899.0409200927.53b7ed8f@posting.google.com...
>>>
>>>>hello
>>>>
>>>>I'm experiencing serious performances issue when using XMLA 1.1 SDK on
>>>>queries returning large answer sets.
>>>>
>>>>My query is retrieving a large number of facts (19) accross 5 members
>>>>on dim 1, and about 1/2 of leaf level members of a parent-child cube
>>>>on dim2.
>>>>There is not computation except the grouping of 6 periods on the time
>>>>dim3. Dim1 and dim2 are parent child dimensions, in order to support
>>>>non-additive measures. The cube is 100% MOLAP, 500 Mb large, with
>>>>15000 items the dim3 and 250 on dim2, 19 facts and
>>>>
>>>>When I run the query over PTS I get a reponse within 30 sec. The same
>>>>query using PTS over HTTP takes 40 to 45 sec. Using a XMLA front end,
>>>>the query is still not completed after 45 min ! during my tests, the
>>>>client tool, the xmla sdk, and the As server are all on the same box.
>>>>
>>>>I checked with multiple tools (sample applications (mdx, xmla),
>>>>proclarity, bristol excel plugin, custom code using VB.Net), and I get
>>>>consistent measures accross access methods with all front end tools.
>>>>
>>>>When I submit queries that return fewer cells, the performance between
>>>>PTS, PTS over HTTP and XMLA is about the same, even on leaf-level on
>>>>cubes above 600 Mb.
>>>>
>>>>My config is a dual Xeon server, with 2 Gb ram. AS 2000 SP3 and XMLA
>>>>1.1 server is running under Win2003 SE. IIS6 had not the compression
>>>>feature activated.
>>>>
>>>>What could be the settings I could check/change to improve the query
>>>>performance ?
>>>>
>>>>Any help would help !
>>>>
>>>>Thanks
>
>
>

• Previous message: Akshai Mirchandani [MS]: "Re: Bad performance for MDX query over XMLA"
• In reply to: Akshai Mirchandani [MS]: "Re: Bad performance for MDX query over XMLA"
• Messages sorted by: [ date ] [ thread ]