Connection Pooling on Analysis Services

Please may I have your assistance with the following issue. Let me 1st describe the scenario. The front end uses BEA Web Logic which queries an IIS Web Service that in turns queries MS Analysis Services 2005.

The Web Service builds the MDX queries dynamically and returns the results in XML.

Each time we run a query we open a connection using ADOMD, fire the MDX to return the result set then close the connection. However we don't think this will provide the best performance. So

1. Does the AS OLEDB provider handle connection pooling internally?

2. If not, is it possible to implement connection pooling in code inside our web service?

3. Are there other ways to implement connection pooling? for example does using HTTP connections automatically provide us with connection pooling?

Thanks

Here is an article about implementing connection pooling in AS2000, the same should apply for AS2005.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_connpooling.asp

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Many thanks for the document. Please have a look at the following.

http://www.codecomments.com/archive364-2005-1-379959.html

What would be ideal is when the web service starts this will automatically create a fixed number of connection pools to an AS Db

Our objective is to be able to reuse these connection pools by different users connecting to the same web service, obviously not simultaneously. The above suggests that the connection pool is destroyed?

Any thoughts would be greatly appreciated

Thanks

John

|||

No.

The post you mention suggests only that you should apply a bit more logic beyond simple 3 step;

1. Application starts.
2. It creates pool of open connecitions size N.
3. Pool of connections is destroyed when you shut down your web service.

It suggests you implement logic in your application that allows your application to grow conneciton pool above pre-set limit N:
In beginning your conneciton pool is N , if application used all of the connections from the conneciton pool and still needs more connections, connection pool will grow to N +x ... Your application will periodically check if it needs to srink connection pool back to N.

As for the different users connecting to your application. You can re-use connections if application is using same credentials to retrevie data from Analysis Server.
For instance if user A and B connecting to your web service running under W credentials. If your connections are opened under W , you can re-use them . If you open connections under A , you should not let B re-use that conneciton , otherwize you might show B too much data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward, Many thanks for your tips and based on your recommendation (to pursue AS 2000) downloaded the MS XML FOR AS SDK (AS 2000) and started to look at the various options

We used the reference to this

Public Shared p As New MSXmlAnalysisSCLib.ADOConPool

p.MaxSessions = 5

cnadomd = p.GetConnection("Data Source=myASServer" & ";Provider=msolap.3; initial catalog=myASDb")

Unfortunately we dropped the above method because there are no options to use XML? This will mean we would need to read the cells and construct our own XML string

Then we looked at the option of using http connection using the latest AS 2005 connection provider which seems to be set up to use connection pooling

conn.ConnectionString = "Data Source=http://myWebServer/OLAPhttp/msmdpump.dll" & ";Provider=msolap.3; initial catalog=myASDb"

As the content of the msmdpump.ini file suggests ::

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>10</ConnectionPoolSize>

<MinThreadPoolSize>1</MinThreadPoolSize>

<MaxThreadPoolSize>10</MaxThreadPoolSize>

<MaxThreadsPerClient>5</MaxThreadsPerClient>

</ConfigurationSettings>

However from perfmon as well as SQL profiler, when issuing multiple connections simultaneously using the above http method it will use the same connection Id, but create multiple ‘current user sessions’. Also the observed execution time was rather slow.

The alternative is to use the following

conn.ConnectionString = "Data Source=myASServer;Provider=msolap.3; initial catalog=myASDb"

which is observed to work correctly when we use multiple connection simultaneously and performs well.

However the issue I have with the above is where do I instantiate the above so that I can re-use the connection. I noticed in IIS we can configure application pooling.

A low level example would be greatly appreciated, if you don’t mind?

Thanks

John

|||

First you can try and increase the ConnectionPoolSize in the msmdpump.ini .

Creating a single connection and having it be reused across several sessions should have comparable or better performance to opening new TCP connections all the time.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.