Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106

    Unanswered: Multiple Ado Connections

    Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!

    Regards.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Multiple Ado Connections

    They should close connections when the retrieving of data is completed
    (or updates, or inserts are completed)

    one connection per user is the best way to control connection leaks
    (when developpers forget to close an unusefull connection)

  3. #3
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
    Thanx for the kind response.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    programs are almost always "linear"
    > open connection
    > retrieve data1
    > close connection

    > open connection
    > retrieve data2
    > close connection
    ...


    even if you've got 300 users there's rarely 300 connections
    opened at the same time because they are closed most of the time
    (except if users do big SELECTs on your database connections will
    be opened longer)


    Originally posted by TALAT
    Means that they can open multiple connections for retrieving data for multiple forms however the connections must be closed later. What about the max-worker threads. Is there any need to change the settings, for i have more that 300 SPIDs at a time on the server.
    Thanx for the kind response.

  5. #5
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    The developers(genius) say that they close the connections in an error handler routine only, i.e., if an exception is thrown; they have written the code to close the connection for that particular form, otherwise not. What a nice coding technique they have adopted.

    Regards.

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    they could open a connection and leave it opened
    but they should reuse this connection
    instead of reopening new ones

    this could be an other way to control the connection leaks


    > start program : open ONE connection
    > window1
    > window2
    > use program
    > use program
    ...
    > end program : close THE connection


    in this case
    Nb users = Nb connections


    but if you have for example a max nb of connections at 100
    only 100 users can connect at the same time

    but with the other solution (open/close unuseful connection)
    you can have more users than the max connections (100 in this example)
    the users are not connected all the time of the use of the program
    and they do not connect at the same time
    if the max connections is reached a user just has to wait a little for a
    connection to be liberated by an other user

  7. #7
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    good luck with your consuming-connections-developper

    just say to him that a connection is a very precious thing to waste
    many users dreamed of having one free
    and db-administrator cannot multiply them indefinitly


    If ONE should use a connection
    ONE should use it shortly
    ONE should think of liberating it rapidly
    ONE should carefully close it
    ONE should know that ONE is not alone
    For MANY use the program
    Even if only ONE develops it

  8. #8
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    I shall rather force them to stop doing this and to change their coding techiniques. Thanx for guiding and responding so quick. Thanx again!!


    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •