Connection pool mode and Performance

[Former Member]

All,

I would like your advice to understand the performance impact of connection parameters. We have BOXI 3.1 base version and SQL Server 2005 database for our data source.

In the universe connection configuration parameters the connection pool mode i have 3 options

1. Disconnect after each transaction

2. Keep connection active for 'x' minutes.

3. Keep the connection active during whole session.

I rejected 1 - because in my understanding establishing a connection session /disconnect does add a overhead on SQL Server - so in this case every single query whether simple or complex will contribute.

I choose 2 currently for 10 mins - so that the same connection session to DB while active can be used by other queries once the others are completed.

I have been told that 3 is the best choice for high performance. However i am wondering how it determines the duration of entire session? Does it come from Universe -> Parameters -> Limit Execution time? Does this allow re-use it once the current query completes running? Any limitations i must be aware of?

Looking forward to your feedback.

[former_member793810 · Accepted solution]

Hi Ramaks,

Here is some info regarding your issue....

You define the life-time with the Connection Pool Mode and Pool timeout parameters. By default, the Connection Pool Mode is set to Keep the connection alive for, with a default Pool timeout of 10 minutes.

Note:

Business Objects recommends you keep the default connection life-time. The universe building process slows significantly if the Connection Pool Mode is set to disconnect after each transaction. Disconnecting after each transaction also impacts key end-user workflows such as working with hierarchical list of values.

The connection life-time can have a significant impact when working with SAP NetWeaver BW.

However, connection life-time can also impact updating existing universes with changes in the BW Query. This is because the OLAP BAPI interface builds a metadata cache on the client side every time a connection to SAP NetWeaver BW is established. This cache is only emptied when the connection closes.

To minimize the risk of metadata cache being desynchronized with SAP BW Query updates, you can change the Pool timeout from 10 minutes to 1 minute.

When working in parallel editing BW Queries and mapping new universes to these queries, it is recommended that you close Designer (so that universe connections are also closed and the metadata cache is emptied) before building any new universes to take into account changes that were just made on the BW Query side.

Bashir Awan

[Former Member · Accepted solution]

Bashir,

I need some clarification as i am not sure if i understood all these points.

So BO recommendation would be " Keep the connection active during whole session" - So how is this session duration determined?

Would this not have any impact on the data server? Can it exhaust all available connections and not entertain any new one?

About OLAP universes- am i to understand that metadata cache is built during initial connection which is then emptied only when connection used. I see this not having any performance impact.

Thanks

[Former Member · Accepted solution]

I would probably go some version of option two. But if you really want to test performance, I'd suggest running some real world tests with your data. I'd not expect much performance gain from anything other than moving away from disconnect on each transaction and even that may be minimal.

[former_member793810 · Accepted solution]

According my practice which is based on SAP Knowledge if you have your Bex Query open and you are working on it then choose first option.

If you have your Bex query closed then choose the third option.

Bashir Awan