Re: Bad performance for MDX query over XMLA

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: ErwanB (ebellec_at_yahoo.com)
Date: 09/21/04 

Date: 21 Sep 2004 02:18:10 -0700

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

Relevant Pages

  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • Re: High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... We have encountered similar problems not limited to Excel, but every client. ... occur when there are many dimensions nested on each other on a single axis ... > Performance Guide to optimize the query, the cube, the server, etc. ... > While the query was grinding, the server was doing absolutely nothing. ...
    (microsoft.public.sqlserver.olap)
  • Re: Delete Query Does Not
    ... When I try to run the SQL query below it gives me the ... Cannot group on fields selected with '*' (client) ... INNER JOIN meals ... Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Report filtered on a calculated field
    ... Make a query to determine who owes you. ... make this into a Totals Query. ... now you can base your first report directly on this query. ... totals for each client, also define a Client footer section as well. ...
    (microsoft.public.access.reports)