none
ADO.NET Pooling Question RRS feed

  • Question

  • Hi,

         I have read too many times "You should only open a connection immediately before you need to access the database and then close it as soon as you are done accessing the database."

     

    I did some test to see how important is to follow that statement for getting good performance database access, but the results I got does not show that statments it true. Why ?

     

    This is the test I run (using .NET 2.0 and SQL Express, Pooling=true and a Max Pool Size=10 for the connection String):

     

    long start = DateTime.Now.Ticks;

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

    {

          connection.Open();          //    ***

          getUsersDataSet();

          connection.Close()          //    ***

    }

    long end = DateTime.Now.Ticks;

    long total = end - start;

     

    Using the connection.Open() and connection.Close() I got the same amount of ticks that I got without using them.

     

    Shouldn't I get much bigger ticks without Open and Closing the connection ?

    It is supposed if I don't call Close, the connection does not get back to the Pool, so the next time I user the connection, the connection pool must create a new one, right ?

     

    Someone could give me some light on this issue ?

    Am I doing something wrong ?

     

    Thanks in advance for your help.

     

    Regards.

     

    Sunday, July 15, 2007 1:18 PM

Answers


  • I don't think I understand your test. A new connection is not created unless there is no available connection in the connection pool or you provide a different connection string (or disable connection pooling).
    Tuesday, July 17, 2007 12:06 PM

All replies

  • I didnt read u r mail line by line i saw the pooling for that one my ans is pooling will be managed by database(It may be sqlserver or oracle ...). Dont mention the poolsize if u set the poolsize as per my knowldge as per the mentioned value that no of users can access u r applicaion wts abt the remaining users. so leave it

    If this information is useful kindly give the  reply

    Baba
    Monday, July 16, 2007 5:50 AM

  • I don't think I understand your test. A new connection is not created unless there is no available connection in the connection pool or you provide a different connection string (or disable connection pooling).
    Tuesday, July 17, 2007 12:06 PM
  • Thanks Paul, after reading your reply I realized my code is wrong. I think I should get the data from different threads.

     

    This test does not have any sense, and the results I got are wrong.

     

    Thanks...

    Tuesday, July 17, 2007 12:19 PM