To connect to a database.

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.

Re: To connect to a database.

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:
b5a3f06b-7f58-429a-95ff-e3b3dca3cff5@l1g2000hsa.googlegroups .com...
> 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.
>

Re: To connect to a database.

"jc" wrote in message
news:b5a3f06b-7f58-429a-95ff-e3b3dca3cff5@l1g2000hsa.googleg roups.com...

> 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

Re: To connect to a database.

"Patrice" wrote in message
news:u4cY1vEVIHA.2000@TK2MSFTNGP05.phx.gbl...

> The thing to check is if your DB supports connection pooling...

The OP is using Oracle which definitely does support connection pooling, but
only if accessed correctly:
http://weblogs.asp.net/ngur/archive/2004/01/21/61207.aspx


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Re: To connect to a database.

"jc" wrote in message
news:b5a3f06b-7f58-429a-95ff-e3b3dca3cff5@l1g2000hsa.googleg roups.com...
> 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

Re: To connect to a database.

On 11 ene, 10:11, "Leon Mayne" wrote:
> "jc" wrote in message
>
> news:b5a3f06b-7f58-429a-95ff-e3b3dca3cff5@l1g2000hsa.googleg roups.com...
>
> > 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 require=
d
> for the page, close the connection, and then populate all the controls fro=
m
> the data. Then you are using the connection for the least amount of time a=
nd
> not opening and closing connections:
>
> CREATE PROCEDURE uspPopulateOrderPage
> @pintOrderId INT
> AS
> -- Get the listbox data
> SELECT
> =A0 =A0 Id, Description
> FROM
> =A0 =A0 tblListItems
>
> -- Get the order details
> SELECT
> =A0 =A0 CustomerName,
> =A0 =A0 MoreInformation
> FROM
> =A0 =A0 tblOrder
> WHERE
> =A0 =A0 OrderId =3D @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.