How Many SQL Connections Should I Use?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • db.guru
    #1

    How Many SQL Connections Should I Use?

    Hello,

    I was wondering what the best practices are as far as how many
    connections one should use for a data-driven app. Here's my situation:
    I have a business object that I load data into, this object also has 3
    collections which I also load data into (via their own method). Right
    now each method has its own connection that uses a datareader to add
    the items to the collection. I can't use the same connection between
    the parent object and my 3 collections because datareaders require
    their own connection. Is it ok to open 4 seperate connections just to
    load one object of data?

    What is the best practice? Anybody have any links where this situation
    is discussed? Any help would be appreciated. Thanks!

    Tags: None
    • Marina Levit [MVP]
      #2
      Re: How Many SQL Connections Should I Use?

      Once each collection finish loading its data - wouldn't it close the
      datareader? In which case, you would never have a situation where code was
      trying to use a connection that still had an active datareader on it.

      Regardless of this, each method that needs to load data should open up a
      connection, get its data, and close the connection. The connection should
      not hang around in an open state if it's not being used.

      "db.guru" <aaron.varga@gm ail.com> wrote in message
      news:1142453153 .819785.204470@ e56g2000cwe.goo glegroups.com.. .[color=blue]
      > Hello,
      >
      > I was wondering what the best practices are as far as how many
      > connections one should use for a data-driven app. Here's my situation:
      > I have a business object that I load data into, this object also has 3
      > collections which I also load data into (via their own method). Right
      > now each method has its own connection that uses a datareader to add
      > the items to the collection. I can't use the same connection between
      > the parent object and my 3 collections because datareaders require
      > their own connection. Is it ok to open 4 seperate connections just to
      > load one object of data?
      >
      > What is the best practice? Anybody have any links where this situation
      > is discussed? Any help would be appreciated. Thanks!
      >[/color]


        Comment

        • db.guru
          #3
          Re: How Many SQL Connections Should I Use?

          Well, the collection loading happens within the main object, which uses
          a connection and a datareader to load its own data. I suppose I could
          close the main object's datareader before loading each collection. (not
          sure why I didn't consider that initially...).

          So it's not too resource intensive to open and close a connection for
          each method that loads data?

            Comment

            • Gman
              #4
              Re: How Many SQL Connections Should I Use?

              My understanding (and I would welcome being corrected) is that if you
              open a new connection using the same connection string as an existing,
              open connection then connection pooling kicks in. Which would mean that
              you wouldn't be opening a new connection, rather using an existing one.

              E.g.

              - Open Connection_A and use it for something.
              - Meanwhile Open Connection_B (which will actually use the same
              connection as Open Connection_A).
              - Close Connection_B - A is in use so it stays open
              - Close Connection_A - no more connections in use so it closes properly

              Now, if you are frequently opening and then closing just one connection,
              I don't quite see how connection pooling can help. I've tried connecting
              to Access (albeit with ADO 2.7 not ADO.NET) and noticed it's
              significantly slower if you keep opening and reopening a connection
              rather than keeping it open.

              I haven't tested it yet with SQL Server (I shall tomorrow) but I have
              been informed that when you connect to Oracle (again not .NET) the
              server assigns resources to that connection. If you close and reopen
              just one connection (i.e. avoiding pooling) you take up fresh resources
              from the server since it doesn't tidy up instantly.

              Again: this is my understanding. Someone please speak up if this is
              nonsense.



              db.guru wrote:[color=blue]
              > Well, the collection loading happens within the main object, which uses
              > a connection and a datareader to load its own data. I suppose I could
              > close the main object's datareader before loading each collection. (not
              > sure why I didn't consider that initially...).
              >
              > So it's not too resource intensive to open and close a connection for
              > each method that loads data?
              >[/color]

                Comment

                Previous template Next
                Working...