Re: AS2005 over HTTP - querying via Excel pivot table extremely slow o
- From: "Laurence Neville" <laurenceneville@xxxxxxxxxxx>
- Date: Tue, 12 Feb 2008 23:16:15 -0000
I never found "the answer" to this but I did notice 2 things:
1. For any given client, the slowness increases dramatically with the size
of the recordset being returned. A 20 row by 20 column set of results is
much slower than a 2 row by 2 column set. Kind of obvious when you realise
that that the entire result set is being re trasmitted from server to client
every query.
2. Excel 2007 as a client seems much faster than earlier versions.
"Andrew F" <AndrewF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A05BEB68-F481-407A-A0B8-66CAB825AFB1@xxxxxxxxxxxxxxxx
I am experiencing similar issues. I can confirm that msmdpump on the server
is responding promptly - the delay is in the transfer/client.
The delay seems to be linked to using basic authentication. The client
(excel MS olap addin) is never remembering to transmit the basic auth so
every second packet is a HTTP/1.1 401 Unauthorized, where upon the client
re-transmits it's request with basic auth, before forgetting it again the
next packet.
I replicate the same thing using basic auth through ADODB in a VBS script.
Cheers
Andrew
"Laurence Neville" wrote:
I have a small cube on AS 2005 SP2 (9.00.3200) and I have enabled access
via
HTTP using IIS on the same server (Win 2003 server). The server is a dual
Xeon 3.0 Ghz processor w. 4GB of memory and is not busy (CPU less than 5%
consistently). The max number of cube aggregations have been designed
(i.e.
to 100% performance increase).
When I browse this cube locally using VS2005 it returns data very
quickly.
Drilling down in a dimension, selecting dimension members, adding
filters -
the new data is alway returned in less than 5s and the CPU is hardly
used.
However, when the same operations are performed through an Excel pivot
table
on a remote PC connected to the cube over HTTP, they take much much
longer.
The PC has a broadband connection to the internet and web browsing is
pretty
fast. An example:
The Time dimension contains 5 years in the top level, and each year
contains
12 months in the next level and then days and hours in the levels below.
With Time on the Rows area, drilling from year to month takes 1s locally
but
60s remotely. Drilling from month to day takes 1s locally but 100s
remotely.
I monitored the HTTP responses and requests on the PC using Fiddler2. The
amount of data coming back in the responses is very low - less than 100K.
The delay is between the request and the response. After the HTTP
response
is received Excel updates the Pivot table pretty quickly.
This makes me think the delay must be due to one or more of the
following:
1. Transmission of request and/or response over internet
2. Communication between IIS and AS on the server
3. Processing of query by AS on the server
1 seems unlikely because the amounts of data are so small. 2 seems
unlikely
because the 2 apps are on the same server. But 3 also seems unlikely
because
the same operations performed in VS2005 locally are very quick. Could it
be
something to do with the fact that Excel is designing the queries rather
than VS? I can't unfortunately install Excel on the server, or connect VS
to
the server from the PC.
Does anyone have any explanations and suggestions for what can I do to
improve performance?
.
- Prev by Date: PULL DATA FROM CUBE TO TABLE
- Next by Date: Re: Custom sum
- Previous by thread: PULL DATA FROM CUBE TO TABLE
- Next by thread: Re: Custom sum
- Index(es):
Relevant Pages
|
Loading
