sql server connect: Hi there,

I have a VB6 application working over a SQL Server 2000 database. since a
long time ago, I am experiencing serious performance problems, that I still
can't resolve. Normaly, the performance problems are related with database
design, query design, application design an hardware. The last one is
excluded, since we have a HP ML350 (double XEON) with 2.5GB RAM as server.
So the problem must result of defective design. Therefore, I have some
questions that I hope to be answered.

DatabaseCode Design:

1. As the VB6 application starts, a connection is created and this same
connection is used in all application processes. Is recomended using always
a new connection? Shall I close the connections and record sets, and recreat
it every time I need to access the database?

2. There are always10-15 workstations using the applications. All of
them access the database with 'sa' user. Is recomended using diferent users
in each workstation? For example, is better using the correspondent Active
Directory user, and then using Windows Authentication Mode?

I have this doubts because I executed some queries and I have found that,
even when there are only 3-4 users working, there are allways like 60, 80 or
even 100 open database connections. Processes of 'sa' user 'Awaiting
command' are even more! This is not normal, I supose.

Can anyone help me out? This is getting out of my nerve...

Thanks!

Regards,

Marco Pais

[quoted text, click to view]
Yes, In most of th application you will release the sql server connection as
soon as you are done with it.
It is generally a bad ideas to leave the connections open for a long time.
Don't worry about the over head of creating a new connection every time you
need it.
SQL Server client's 'connection pooling' will help you out here.

[quoted text, click to view]
It will not make any difference as your application is running on different
workstations, you will not be able to leverage client side 'connection
pooling'
The technique of using a single login normally helps in a 3 tier
architecture where the middle tier can take advantage of the 'connection
pooling'

hth
--
Vikram Vamshi
Eclipsys Corporation