none
ado.net connexion pool not working RRS feed

  • Question

  •  

    Hello

     

    I use sql adapters to call stocked procedures in SQL Server 2005

    if i watch the SQL transactions by monitoring SQL Server, i see every transaction open a new connection et close it after.

     

    So the ado.net connection pool seems not to work at all

     

    Though the msdn doc says the pooling is activated by default I tried to put the pooling=true in my web.config file but it's the same

     

    Who can help me? The performance of my application is so bad beacause of that, and my client is not happy

    Tuesday, June 17, 2008 11:18 AM

Answers

  • I am also architect, so we should be on a same page. No, it does not mean that pool did not work at all. TableAdapter uses pretty good pattern when working with database connections: open connection - get data - close connection. That is what needs to be done everywhere in application to minimize database resource usage and avoid database connections leaking, and that's why connection pool was invented to reuse previously opened connections. In SQL Profiler you will still see your connection opened and closed, but it will be moved to pool and reused after first connection is established and assuming that your application uses same connection string. Did you check how much time application spends to open connection? It might be very close to zero and you need to investigate your query instead.

    Thursday, June 19, 2008 10:31 AM
    Moderator

All replies

  • Hi,

     

    I would recommend you to read this article first:

    SQL Server Connection Pooling (ADO.NET)

     

     

    HTH,

    Suprotim Agarwal

     

    Tuesday, June 17, 2008 11:36 AM
  • as i saif i already kwow all these articles

    but i still have the problem.

     

    My question is how to investigate and understand the problem with dataadapters

    Tuesday, June 17, 2008 12:56 PM
  • Using pooling does not mean new connections will not be opened at all. They will be, but information about previously opened connection will be reused and it works really fast. What makes you believe that performance of applcation suffers because of connection reuse? Did you check how much time SQL Server spends to open connections? Did you check how many opened connections you have against database and you do not have connections leaking situation?

     

    Wednesday, June 18, 2008 10:01 AM
    Moderator
  • I am .net architect , so i did tests with a SQL Server administrator

    The is quite simple, i create a tableadapter who calls a simple stored procedure "select * from societe"

     

    And i do a .net loop on this

     

    for (int i = 0; i < 1000; i++)

    {

    DataSet1 v_dst = new DataSet1();

    new DataSet1TableAdapters.SOCIETELoadAllTableAdapter().Fill(v_dst.SOCIETELoadAll);

    }

     

    So the stored procedure is called 1000 times, and the database administrator told me after the test that also 1000 connections were opened and closed on SQL Server

     

    So the pool is not used at all.

    Wednesday, June 18, 2008 10:07 AM
  • I am also architect, so we should be on a same page. No, it does not mean that pool did not work at all. TableAdapter uses pretty good pattern when working with database connections: open connection - get data - close connection. That is what needs to be done everywhere in application to minimize database resource usage and avoid database connections leaking, and that's why connection pool was invented to reuse previously opened connections. In SQL Profiler you will still see your connection opened and closed, but it will be moved to pool and reused after first connection is established and assuming that your application uses same connection string. Did you check how much time application spends to open connection? It might be very close to zero and you need to investigate your query instead.

    Thursday, June 19, 2008 10:31 AM
    Moderator