none
Improve sql server connections ADO.NET RRS feed

  • Question

  • Hi,

    I'm developing a .NET application (Service application) that will listen to clients and insert records to a sql server database frequently. Client requests are irregular. Say at one instance the service will receive contentious 100 messages that has to be inserted to the sql server and waits for about 10 mins for the next message to arrive.

     

    I have used a table adapter to insert the records. Since the table adapter handles the connection to the database I don't have any control over it and each time when it inserts a record the application opens a connection to the DB

     

    I wonder whether there is a way to improve this and avoid opening closing a connection to the server every time. Probably keep a connection open for a while.

     

    What is the best way to handle this situation.

     

    Many thanks.

    VJ

     

    I'm using ADO.NET 2.0 sql server 2005

    Friday, June 27, 2008 1:58 PM

Answers

  • I believe you need to pay attention to the pooling of connection. This is built-in functionality in SqlConnection and you could improve performance of application if your pool managed properly. I would not keep connection opened for several reasons:

    1. It could lead to database connection leaking in a case of application errors or crashes.
    2. There is no reason to hold expensive connection resources opened, as it affects performance as well

     

    Using connection pooling allows reusing previously opened connections and opening new one takes almost no time and will not affect performance of your application greatly. I would keep connection opened only in a case if I need to execute multiple statements and I know it in advance what needs to be executed.

     

    What makes you believe that it is connection that causes performance issues? Did you do profiling? You could improve overall performance of your insert using next tips:

     

    1. Execute your SQL statements/stored procedures using SqlCommand object directly, not SqIAdapter or Table Adapter. You could use ExecuteNonQuery method of this class to execute all action SQL statements that do not return any records.

    2. Use stored procedures instead of SQL statements. It is not huge performance improvement on one call, but on repeated calls you could get some improvement.

    3. Run query execution plan for your SQL statements and see where they spend most of the time. Try to improve that area, if possiNe.

     

    Tuesday, July 1, 2008 2:19 AM
    Moderator

All replies

  • You can use SqlConnection object to manually open and close connections to sql server. Use SqlCommand class to issue commands.
    Saturday, June 28, 2008 10:00 PM
  • Hi,

    Thanks for the reply. So you mean keep the connection open for a certain time and execute the insert command few times using that opened connection. Then close the connection after a specified time.

     

    Can I use the connection time out property to specify the time to close the connection after waiting for a certain time.

     

    thanks.

    Vj

     

    Monday, June 30, 2008 7:38 AM
  • It is recommended that you don't have a connection open for too long time but if you are going to issue several commands in a small period it is better to open and close connection once rather then to open and close for each command. SqlConnection has a propery called ConnectionTimeout but it serves different purpose.
    Monday, June 30, 2008 8:08 AM
  • Hi,

    Correct me if I'm wrong. In my scenario I believe that keeping a connection open to process few insert operations are efficient. But I don't know a proper; say a design pattern to handle this. Is there any material that I can refer to that you know which will help to understand optimizing this kind of operations?

     

    thanks v much.

    VJ

     

     

    Monday, June 30, 2008 8:50 AM
  • I believe you need to pay attention to the pooling of connection. This is built-in functionality in SqlConnection and you could improve performance of application if your pool managed properly. I would not keep connection opened for several reasons:

    1. It could lead to database connection leaking in a case of application errors or crashes.
    2. There is no reason to hold expensive connection resources opened, as it affects performance as well

     

    Using connection pooling allows reusing previously opened connections and opening new one takes almost no time and will not affect performance of your application greatly. I would keep connection opened only in a case if I need to execute multiple statements and I know it in advance what needs to be executed.

     

    What makes you believe that it is connection that causes performance issues? Did you do profiling? You could improve overall performance of your insert using next tips:

     

    1. Execute your SQL statements/stored procedures using SqlCommand object directly, not SqIAdapter or Table Adapter. You could use ExecuteNonQuery method of this class to execute all action SQL statements that do not return any records.

    2. Use stored procedures instead of SQL statements. It is not huge performance improvement on one call, but on repeated calls you could get some improvement.

    3. Run query execution plan for your SQL statements and see where they spend most of the time. Try to improve that area, if possiNe.

     

    Tuesday, July 1, 2008 2:19 AM
    Moderator