none
Single Connection VS Multiple Connection RRS feed

  • Question

  • Hi,

    I have an VB application that runs daily with millions of messages to be pocess read/write to the database. Which of the following will be more efficient?

    1. In a sub, open single connection, create and execute SQL statements, close connection.

    2. divide the work in to multiple subs, connect to the database from individual sub.

     

    Tuesday, May 3, 2011 8:12 PM

Answers

  • If each transaction (method 1) is short, and there is no relation between two different runs of method 1, then multiple connections will probably give you a higher throughput, because it allows more parallel use of different resources.

    However, if you have to break up your work package to be able to use multiple connections, then you probably shouldn't do that, because SQL Server is especially good at finding the fastest execution for a big job.

     


    Gert-Jan
    • Marked as answer by ASP808 Tuesday, May 10, 2011 7:23 PM
    Wednesday, May 4, 2011 3:37 PM

All replies

  • If each transaction (method 1) is short, and there is no relation between two different runs of method 1, then multiple connections will probably give you a higher throughput, because it allows more parallel use of different resources.

    However, if you have to break up your work package to be able to use multiple connections, then you probably shouldn't do that, because SQL Server is especially good at finding the fastest execution for a big job.

     


    Gert-Jan
    • Marked as answer by ASP808 Tuesday, May 10, 2011 7:23 PM
    Wednesday, May 4, 2011 3:37 PM
  • You need to find out what is taking the time. You can probably only do that by testing - then you will know what to concentrate on.

    You could spend time splitting the application up int odiferent threads only to find that it doesn't affect the throughput much. The time initially is probably better spent trying to make a single thread efficient.

    Wednesday, May 4, 2011 5:57 PM
  • Any progress?
    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Monday, May 9, 2011 7:24 PM
    Moderator