hi can anyone help me to find how to effectively share database connection objects from .net connection pool. Mine is a web appplication and i have to limit the connection pool size to a particlar number too(say 20 connections) .so that requests from all client browser share this 20 connections. And i have read that if more than 20 request comes at same time the rest of the requests are forced to wait till any conection in pool is freed. can i have a better way of managing this connections? My main aim is to process as many requests in webserver without giving much overload on database server. can anyone help me please..
2
-
This link might be usefule to you for performance aspect. linkTechnology Digestified– Technology Digestified2011-11-18 05:53:23 +00:00Commented Nov 18, 2011 at 5:53
-
Can you cache any of the queries to reduce the number of requests going to the database? How many web servers and database servers are there?SliverNinja– SliverNinja2011-11-18 05:54:54 +00:00Commented Nov 18, 2011 at 5:54
Add a comment
|
2 Answers
Connections will be automatically pulled from the pool based on the connection string, so if you don't vary your connection string for each user (or other purpose), you will be able to reuse a connection from the pool as soon as you are done with one (note that I am describing the behavior implemented by the pooling code and not anything that you have to do or worry about).
In order to be successful with this, you need to ensure that your connections are only open exactly as long as you need them to be and that they are always, always closed (this is general advice, not just specific to your situation).
If you find that you are running into the connection pool limit, you can trap this error and either tell the user to try again later or spin for a brief period and try the connection again.
2 Comments
Merlyn Morgan-Graham
+1; Let the connection pool do the job it was designed for, and don't get in its way by trying to hold connections open.
Spence
This is good advice. Writing your code to only hit the database when required and to only access data that it actually needs is always going to be good for your system. You might want to look at caching the data as well if possible so you don't hit the DB for stuff you already have that doesn't change often.
The maximum pool size is 100 by default but if you want to reduce this number to just 20, you can specify it on your Connection String.
Using these parameters on your connection string:
Max Pool Size = 20
Pooling = True
You are restricting the pool size to 20.
See here for more details regarding ConnectionString.
