Performance - Database apps, connections, and C#

I have read that making connections is very expensive, and I have read that
keeping connections open is very expensive.
I have a windows client/server App. There will typically be 3-5 client user
s accessing the system at any given time, all on a local area network. When
they are using the system, they might use it for an hour or more. While vi
ewing the data, they will m
ake frequent requests from the database. (In some cases, the screens they a
re viewing will automatically refresh, by sending a new query to the databas
e.) In other words, each client might request new data every 5 seconds or s
o.
Also, there is a service running that automatically monitors machines, and p
uts the recorded data into a database, resulting in a database updata once p
er second from each machine. There are anywhere from 4 to 100 machines.
My question is whether I would be better off to create a database connection
at the time the user logs on, and maintain that connection until he logs of
f, or would it be better to create and use a new connection for every query.
Using a new connection fo
r every query, I get the overhead associated with new connections. Keeping
the connection alive the whole time, I get the overhead associated with main
taining a connection.
Likewise, with the machine monitoring connections, would I be better off ope
ning a connection for each machine, and keeping it alive as long as the moni
toring continued, or would I be better off creating and disposing of it freq
uently?
In case it matters, this is all using Visual C# with ADO.net.Generally, most apps are written today to close the connections when you're
done with them. Web applications especially open and close connections
very frequently. The client will use connection pooling to reduce the
overhead of establishing brand new connections so the perf hit isn't so bad.
Most performace gains are achieved by tuning the queries you're sending to
the server etc. and good database design.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.