Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL 2005 Performace Question

Back to Forum: Tech Off
  • User profile image

    Hi Guys,
       I have finished a working database, for a high preformance operations that require constant checking with the database, and so on

    The way i have it setup is that when ever i need to execute a stored procedure, i would then need to open the connection to SQL server, and then do what i need, then close the connection...
    now there are about 60 stored procedures there or so.  and each are executed sometime at some point in the execution of the SQLClient lifetime. Now I was woundering , if closing, opening, closing, is more efficient, and resources saving, than just having the connection open at the start of the SQLClient, and then when it closes , i close connection with sql?

    so is
    Open , Close,Open,Close.........
    better in terms of perfomance and resources
    Open  .. do your multible work,,,, then Close connection to sql...
    thanks in advance!

  • User profile image

    You could always write a simple application to test which one is more efficient

    Part of what you may want to consider is how many connections your app is making to db (i.e. is it multithreaded and each thread has its own connection).

    I did some testing earlier (over a year ago) with an application and found that the overhead to Open() and Close() repeatedly was _extremely_ minimal.  This means milliseconds on thousands of calls to Open and close vs leaving it open and calling close when totally done.

    So performance wise, unless you really need those extra milliseconds, it comes down to what you want to do style/architecture wise.

    Open/Close repeatedly would lend itself better to refactoring and not needing to wonder if the connection is still open (i.e. trace it through the code, etc.).


  • User profile image

    You might also look into turning on "connection pooling" on the computer that runs the application.  This will keep a connection open, and in the pool, for 60 seconds after you "close" it.  Then if you open another connection while there are connections in the pool, it will just hand you an already-open connection.

  • User profile image

    Maurits wrote:
    Then if you open another connection while there are connections in the pool, it will just hand you an already-open connection.

    As long as all the parameters in the connection string, database, intial catalog, timeouts, user ID and so on are exactly the same.

  • User profile image

    Isn't connection pooling enabled by default?

  • User profile image

    Sometimes. Smiley

  • User profile image

    always Tongue Out

  • User profile image

    thanks guys, for your help

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.