Connection Management

[Ryan P. Kennedy]

Hi:

I have a Point-of-sale application that uses SQL Server2000 for the backend.
Basically, the users perform various functions boiling down to login (check
password from a table) and data entry (insert a food entry). Previously, I
would open a new ADO 2.7 connection to the database each time one of these
types of database accessing functions needed to be performed - but I noticed
that sometimes the DB would freeze the application for 20 seconds or so - or
even cause a timeout error.

To fix this, I open a DB connection when the application first starts,
keeping it open for the life of the application - each time a function needs
to access the DB, it just uses the applications (global) connection that is
constantly open and connected.

This seems to have fixed the problem, however, I am curious, is this an OK
way to handle the connections - keeping in mind that there are four separate
stations - each running the application - at the same time. Therefore, I
have 4 constantly open connections at the same time.


Thanks and regards,

Ryan Kennedy

[Simon Hayes]

Re: Connection Management


"Ryan P. Kennedy" <ryanp.kennedy@ verizon.net> wrote in message
news:lYnBb.4549 $UM4.1037@nwrdn y01.gnilink.net ...[color=blue]
> Hi:
>
> I have a Point-of-sale application that uses SQL Server2000 for the[/color]
backend.[color=blue]
> Basically, the users perform various functions boiling down to login[/color]
(check[color=blue]
> password from a table) and data entry (insert a food entry). Previously,[/color]
I[color=blue]
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I[/color]
noticed[color=blue]
> that sometimes the DB would freeze the application for 20 seconds or so -[/color]
or[color=blue]
> even cause a timeout error.
>
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function[/color]
needs[color=blue]
> to access the DB, it just uses the applications (global) connection that[/color]
is[color=blue]
> constantly open and connected.
>
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four[/color]
separate[color=blue]
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.
>
>
> Thanks and regards,
>
> Ryan Kennedy
>
>[/color]

I don't know much about ADO, but it sounds like you're describing a form of
connection pooling, which is certainly a very common way to manage
connections from multiple clients.

Simon

[BenignVanilla]

Re: Connection Management


"Ryan P. Kennedy" <ryanp.kennedy@ verizon.net> wrote in message
news:lYnBb.4549 $UM4.1037@nwrdn y01.gnilink.net ...[color=blue]
> Hi:
>
> I have a Point-of-sale application that uses SQL Server2000 for the[/color]
backend.[color=blue]
> Basically, the users perform various functions boiling down to login[/color]
(check[color=blue]
> password from a table) and data entry (insert a food entry). Previously,[/color]
I[color=blue]
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I[/color]
noticed[color=blue]
> that sometimes the DB would freeze the application for 20 seconds or so -[/color]
or[color=blue]
> even cause a timeout error.
>
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function[/color]
needs[color=blue]
> to access the DB, it just uses the applications (global) connection that[/color]
is[color=blue]
> constantly open and connected.
>
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four[/color]
separate[color=blue]
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.[/color]

Connection pooling, the sharing of a single connection among components of
an application, is very common and a good design principle. It is
particularly great for web based/ASP applications, in order to conserve
resource. Persistant connections, keeping a connection open even when not in
use, is something I shy away from in my client server and my web based app
designs. I prefer to create a connection, pool it, and then open and close
it as needed.

It sounds like you are looking for a solution to a symptom, and not your
problem. If I were you, I would investigate the reason your app is timing
out and solve that.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com