To connect to a database.

Discussion in 'ASP .Net' started by jc, Jan 11, 2008.

  1. jc

    jc Guest

    Hi there:

    Reading from internet i found that the best way to use a database is
    to connect and disconnect on every single database request, so if you
    are loading a listbox manually from a database and on the same page,
    loading a grid from the database, then the best way is to connect to
    database, load the listbox, disconnect, connect again, load the grid
    and disconnect.

    what is the cause because to reconnect many times is more
    efficient?.

    I tried running some test on about it,connecting once on page request
    is almost 20% more fast that connecting many times.

    Also, i checked the number of session open on oracle and in both
    method did keep the same number of session. In any case, aspnet will
    keep many connections opens (persistent?). May be this method of open
    and close for request is efficient with sqlserver but other database.
     
    jc, Jan 11, 2008
    #1
    1. Advertising

  2. jc

    Patrice Guest

    20 % from what ? Even 100 % wouldn't mean anything. If it's small enough
    this is anyway non visible to the end user...

    This is not really "more efficient". It just much simpler and it guarantees
    that connection are released as soon as possible...

    If you keep the connection opn :
    - any failure to close the connection could cause problem
    - it could be closed later than needed making this connection not available
    even though not used
    - if you have some processing between those openings, this particular
    connection is not available even though not used at this time...

    As most often this is a matter of trade off... The thing to check is if
    your DB supports connection pooling...

    --
    Patrice

    "jc" <> a écrit dans le message de news:
    ...
    > Hi there:
    >
    > Reading from internet i found that the best way to use a database is
    > to connect and disconnect on every single database request, so if you
    > are loading a listbox manually from a database and on the same page,
    > loading a grid from the database, then the best way is to connect to
    > database, load the listbox, disconnect, connect again, load the grid
    > and disconnect.
    >
    > what is the cause because to reconnect many times is more
    > efficient?.
    >
    > I tried running some test on about it,connecting once on page request
    > is almost 20% more fast that connecting many times.
    >
    > Also, i checked the number of session open on oracle and in both
    > method did keep the same number of session. In any case, aspnet will
    > keep many connections opens (persistent?). May be this method of open
    > and close for request is efficient with sqlserver but other database.
    >
     
    Patrice, Jan 11, 2008
    #2
    1. Advertising

  3. "jc" <> wrote in message
    news:...

    > Reading from internet i found that the best way to use a database is
    > to connect and disconnect on every single database request, so if you
    > are loading a listbox manually from a database and on the same page,
    > loading a grid from the database, then the best way is to connect to
    > database, load the listbox, disconnect, connect again, load the grid
    > and disconnect.


    That's the generally accepted wisdom, yes...

    > what is the cause because to reconnect many times is more
    > efficient?.


    Yes, because of a feature of ADO.NET called connection pooling. With web
    applications, "little and often" is definitely the way to go...

    > I tried running some test on about it,connecting once on page request
    > is almost 20% more fast that connecting many times.


    How are you creating / destroying the connections...?

    > In any case, ASP.NET will keep many connections opens (persistent?).


    If your web app has persistent connections with your RDBMS, then you need to
    rethink your connectivity method...


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
     
    Mark Rae [MVP], Jan 11, 2008
    #3
  4. Mark Rae [MVP], Jan 11, 2008
    #4
  5. jc

    Leon Mayne Guest

    "jc" <> wrote in message
    news:...
    > Reading from internet i found that the best way to use a database is
    > to connect and disconnect on every single database request, so if you
    > are loading a listbox manually from a database and on the same page,
    > loading a grid from the database, then the best way is to connect to
    > database, load the listbox, disconnect, connect again, load the grid
    > and disconnect.


    Depends on the situation. In most circumstances it is better to just pull
    back what you need then close the connection and reopen to get any
    additional info. I would certainly close the connection after each query,
    but you could have a Populate() method in your code which calls one stored
    procedure that returns multiple resultsets for all the information required
    for the page, close the connection, and then populate all the controls from
    the data. Then you are using the connection for the least amount of time and
    not opening and closing connections:

    CREATE PROCEDURE uspPopulateOrderPage
    @pintOrderId INT
    AS
    -- Get the listbox data
    SELECT
    Id, Description
    FROM
    tblListItems

    -- Get the order details
    SELECT
    CustomerName,
    MoreInformation
    FROM
    tblOrder
    WHERE
    OrderId = @pintOrderId
     
    Leon Mayne, Jan 11, 2008
    #5
  6. jc

    jc Guest

    On 11 ene, 10:11, "Leon Mayne" <leon@rmv_me.mvps.org> wrote:
    > "jc" <> wrote in message
    >
    > news:...
    >
    > > Reading from internet i found that the best way to use a database is
    > > to connect and disconnect on every single database request, so if you
    > > are loading a listbox manually from a database and on the same page,
    > > loading a grid from the database, then the best way is to connect to
    > > database, load the listbox, disconnect, connect again, load the grid
    > > and disconnect.

    >
    > Depends on the situation. In most circumstances it is better to just pull
    > back what you need then close the connection and reopen to get any
    > additional info. I would certainly close the connection after each query,
    > but you could have a Populate() method in your code which calls one stored
    > procedure that returns multiple resultsets for all the information required
    > for the page, close the connection, and then populate all the controls from
    > the data. Then you are using the connection for the least amount of time and
    > not opening and closing connections:
    >
    > CREATE PROCEDURE uspPopulateOrderPage
    > @pintOrderId INT
    > AS
    > -- Get the listbox data
    > SELECT
    >     Id, Description
    > FROM
    >     tblListItems
    >
    > -- Get the order details
    > SELECT
    >     CustomerName,
    >     MoreInformation
    > FROM
    >     tblOrder
    > WHERE
    >     OrderId = @pintOrderId


    But with this method the code will become a bit spaguetti.

    I checked that close() and dispose() in fact don't close the
    connection but a consecutive open() will not open a new connection.
    So, in both cases, connection are spooled and from the database
    viewpoint (oracle), the number of connections is stable.

    I think that there aren't a simple method but depend on the specific
    case, in my case i will open and close once per page request but still
    will left open the option to open/close connection by request basis,
    just in case.
     
    jc, Jan 17, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Otis Mukinfus
    Replies:
    3
    Views:
    961
    Juan T. Llibre
    Jul 8, 2005
  2. Replies:
    3
    Views:
    40,681
    Roedy Green
    Nov 16, 2005
  3. Replies:
    2
    Views:
    330
  4. Mohsen Pahlevanzadeh

    Qt connect and first connect or unicode

    Mohsen Pahlevanzadeh, Sep 17, 2013, in forum: Python
    Replies:
    3
    Views:
    178
    Mohsen Pahlevanzadeh
    Sep 18, 2013
  5. Mohsen Pahlevanzadeh

    Re: Qt connect and first connect or unicode

    Mohsen Pahlevanzadeh, Sep 17, 2013, in forum: Python
    Replies:
    0
    Views:
    140
    Mohsen Pahlevanzadeh
    Sep 17, 2013
Loading...

Share This Page