Connection pooling for internet apps?

[Guest]

I wanted to post this on the SQL newsgroup, but I get a 'page not found' when
trying to go to that newsgroup. Since it is a C# app, I'll try it here.

I have a C# web application (.NET 1.1). I multi-thread requests to 10
different tables (full text indexed tables) on the same database. These are
done concurrently, not consecutively. In order to get this to work, I have
to open 10 connections to the database. According to the documentation,
pooling is on by default. I get some sporadic errors that make me think that
connection pooling may not be working correctly, or perhaps there is a bug in
connection pooling. For example, I occasionally get an error indicating that
the specified connection is in use. I get a stack dump when this happens,
but it happens in seemingly random locations in my code. I can never
recreate the problem, because when I try to recreate it, it works fine. I
just can't figure out why the code works 99% of the time, but 1% of the time
it will tell me the connection is in use. Other times I get an error saying
that a specified column does not exist in the collection (on sql readers
specifically), even though I know it does. Again, when trying to recreate
the error, it works perfectly. It would appear to be some type of corruption
in the connection, that is the only thing that really makes sense.

 

[KJ]

I too have experienced the seemingly random "connection is in use"
error, and was unable to solve it, except to serialize the database
calls (which is not necessarily a bad idea on a single-processor
machine). Of course, you can turn connection pooling off in the
connection string if need be (Pooling=false) to see if there is a
difference. Another thing to investigate is whether all the objects
used to connect and execute commands are disposed properly.

Hope this helps.

 

[Willy Denoyette [MVP]]

I wanted to post this on the SQL newsgroup, but I get a 'page not found'
when
trying to go to that newsgroup. Since it is a C# app, I'll try it here.

I have a C# web application (.NET 1.1). I multi-thread requests to 10
different tables (full text indexed tables) on the same database. These
are
done concurrently, not consecutively. In order to get this to work, I
have
to open 10 connections to the database. According to the documentation,
pooling is on by default. I get some sporadic errors that make me think
that
connection pooling may not be working correctly, or perhaps there is a bug
in
connection pooling. For example, I occasionally get an error indicating
that
the specified connection is in use. I get a stack dump when this happens,
but it happens in seemingly random locations in my code. I can never
recreate the problem, because when I try to recreate it, it works fine. I
just can't figure out why the code works 99% of the time, but 1% of the
time
it will tell me the connection is in use. Other times I get an error
saying
that a specified column does not exist in the collection (on sql readers
specifically), even though I know it does. Again, when trying to recreate
the error, it works perfectly. It would appear to be some type of
corruption
in the connection, that is the only thing that really makes sense.

Where does the SQL server run on? Same machine as client? What OS?
How many concurent connections are there allowed for SQL?

Willy.

 

[Guest]

I've tried both configurations. Originally they were on 2 seperate servers
on the same network segment, same physicial location, just connected through
a switch. I've since moved them to the same machine hoping to eliminate the
problem, but the frequency and seemingly randomness of the problem has not
changed. I am running on Windows Server 2003 Enterprise, SQL Server 2000
SP4. The original configuration had 1 Gig of Ram on each machine. The
current configuration (where they are on the same machine) has 2 Gig of Ram.
This database is not what I would call extremely busy, but that is obviously
relative. We have had 90,000 visitors to our website in 6 months. That's
about 15,000 per month, or 500 per day. To me, that's low to medium usage.

The application DOES beat the heck out of the database. Depending on
options, it starts up to 15 threads all running multi-threaded. About 8 to
10 of the threads will open independent connections to the database. All
connections are opened sequentially at the start of the process, and when all
threads complete, all connections are closed, then disposed.

The method I use for managing my connections is to have a class called
'global'. This class is instantiated at the start of my web request, at
which point all connections are opened. The connection variables are all
defined at the class level as private variables, NOT static variables. This
should keep each web request completely independent of each other, not? I
mean, is it possible that 2 users at the same time could somehow walk over
each other? If the connections were static, then I would think that is a
possibility, but they are not static.

 

[KJ]

When are the connection objects disposed?

 

[Guest]

I dispose of all objects in the Page_Unload method.