High DB login/logout due to LoadDataSet

Topics: Data Access Application Block
Jun 18, 2010 at 4:46 AM
Edited Jun 18, 2010 at 4:56 AM
Hi,

In our application, for query-based functions, we do not call BeginTransaction() when calling Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames).

However, in the Database.GetOpenConnection(bool disposeInnerConnection), because we never start the TransactionScope, TransactionScopeConnections.GetConnection(this) will be null, and thus it will call GetNewOpenConnection() which will do connection.Open().

To make it worse, we have a loop in one of the query, and that loop calls multiple time of LoadDataSet().

Will this causes high login/logout in the SQL Server side, which in turn causes high SP_RESET_CONNECTION? If so, how can we solve this? Do BeginTransaction() before any loop of query to prevent the open and close of DB connection?

Appreciate any help/advise.

Thank you in advance.

Jun 18, 2010 at 6:18 AM

 I think connection pooling will be a better approach rather than using a transaction since having transaction in your scenario can cause unnecessary locking.

Try setting your minimum pool connection to 50 maximum pool connection to 100.  Observe the process the in the sql profiler.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@Avanade.com

Jun 18, 2010 at 7:06 AM
Hi Sarah

Thanks for your reply.

We are using default pooling, which (my understanding) is default 0 and max 100?

However, am I right to say that, connection pooling will keep the connection connected, but in application when we do connection open() and close(), it will do login and logout at the SQL Server?

What we are facing is, the high volume of login and logout is slowing down the SQL Server:

00000000`048bca38  00000000`0360e8a5 sqlservr!FRedoLoginImpl+0x2c5 

So I am wondering how can we reduce the # of login/logout?

Thank you.
Jun 18, 2010 at 8:10 AM

Yes, by default it's enabled, just realized that the minimum pool connection doesn't really matter in your scenario.  Connection pooling only keeps the connection resources, it doesn't mean those connection resources stay open and connected to the database.

I get now though your concern and as of now, the easiest way to get around that is yes, as you originally thought of, is to use a transaction.  There might be other possible ways though.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@Avanade.com