Connection pool mode and Performance

building semantic layer
1

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.

ramaks (BOB member since 2009-03-31)

2

You are correct in describing option 1, that would be the most expensive option and least performant.

With option 2 the connection will be shared and will only be closed if there is no activity for the time you specify.

Option 3 will keep the connection open while the user session is still active.

Option 2 is your best option. Now, there are always exceptions but in general this is correct.

jwhite9 :us: (BOB member since 2006-07-28)