VB6 Frontend, User connections and SQL Server Processes - 03-31-2005 , 08:29 AM
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.
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...