I have written an ASP.Net Web Service that returns the results of an MDX
query via OleDbDataAdapter. I know adomd.net is preferred, but my query
result times are actually a little slower when I use it.

I had initally opened the connection, made my query and then closed the
connection each time the web method was called. This resulted in the web
method taking an average of 2 seconds per call. (each call is a different MDX
query)

When I opened and added the connection to an Application variable and closed
it in in Global.asax Application_End my web method call time decreased to
about 0.2 seconds. A huge improvement. I guess meaning that there is a large
cost to opening a connection.

Is the practice of storing an open connection in an application variable
acceptable?

Are there any drawbacks ... like connection pooling problems? Is there any
offical guidance on the best way to manage OLAP connections in a Web Service?

Any feedback greatly appreciated.

The issue you will run into is scalability. Placing any COM object in
Application will limit scaleability. It has to do with the way that
concurrency and threading works with ASP.NET. Check any good ASP.NET book.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"billd" <billd (AT) discussions (DOT) microsoft.com> wrote

Thanks for the reply Dave. I used the Application as an example. I could take
the same approach and put it into the user Session, which would give a little
more scalibility. But that is really not my question. My question is if there
is some technique to reduce the overhead of opening a new connection each
time.

Most good ASP books I have read say to close the connection at the first
opportunity (that connection pooling is defaulted to ON with .Net). But from
what I have seen, the latency of opening a new connection, making an MDX
query, and then closing the connection even when the object is pooled is
significant.



"billd" wrote:

Unfortunately, the only way is to cache the connection object as you were
doing.
On the client (and in this case, since you are mid-tier app, your web server
is a client to the Analysis server) we don't support connection pooling.
There are some techniques which you might be able to program into your app
using the ODBO supplied with the XMLA SDK, but it is pretty tailored to just
that version of PTS. See the info here:
http://msdn.microsoft.com/library/de...onnpooling.asp

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"bill" <bill (AT) discussions (DOT) microsoft.com> wrote