连接池问题 [英] Connection pooling questions

问题描述

Hello All，


前段时间，我实现了一个包含简单

连接池的数据访问层。当时，我自己完成了这一切：我创建了N

连接，每个连接与一个工作线程关联，这将执行db命令。池已修复，并且在实例化db访问类时创建了所有连接

。

连接在整个执行过程中保持打开状态。如果此时没有连接

，则请求将被阻止，直到释放一个

连接。


In我的方法，我没有使用SQL服务器连接轮询（我实际上在我的连接字符串中禁用了
）。


这是使用ado在c ++中完成的。现在，我处于需要在.NET中实现db访问层的
的位置。我想知道我可以从.NET获得什么样的支持。


我想实现类似的模型。如果你能够对我的方法和在

ado.net上完成类似工作的方法有所影响，我将不胜感激。我也很感谢你对这个方法本身的评论。


谢谢

CD

Hello All,

Some time ago, I implemented a data access layer that included a simple
connectin pool. At the time, I did it all by myself: I created N
connections, each connection associated with a worker thread that would
execute the db commands. The pool was fixed and all the connections
were created when the db access class was instantiated. The
connections remained opened during the whole execution. If a connection
was not available at the moment, then the request would block until one
connection was freed.

In my approach, I did not use the SQL server connection polling (I
actually disabled it in my connection strings).

That was done in c++ using ado. Now, I am in a position where I need to
implement a db access layer in .NET. I was wondering what kind of
support I can get from .NET.

I want to implement a similar model. I would appreciate if you could
weigh on my approach and on the ways to accomplish something similar in
ado.net. I would also appreciate your comments on the approach itself.

Thank you
CD

推荐答案

我有一些想法：

1 ..这是一个Windows窗体还是ASP.NET应用程序。前者并不真正需要连接池机制，因为整个应用程序可能能够共享一个公共连接。

2 ..如果它是ASP.NET，那么代码就没什么意义了，调试，测试和部署自己的pooler。 ADO.NET的.NET数据提供程序中内置的代码应该可以正常工作。


-

____________________________________

William（Bill）Vaughn

作者，导师，顾问

Microsoft MVP

INETA发言人
www.betav.com/blog/billva
www.betav。 com

请只回复新闻组，以便其他人可以受益。

此帖子按原样提供。没有保证，也没有赋予任何权利。

__________________________________

访问 www.hitchhikerguides.net 获取有关我最新书的更多信息：

Hitchhiker的Visual Studio和SQL Server指南（第7版）

和Hitchhiker的SQL Server 2005精简版指南（电子书）

------------------------- -------------------------------------------------- --------------------------------------------


< cr **** @ yahoo.com在留言新闻中写道：11 ********************** @ f1g2000cwa.googlegro ups。 com ...

I have a few thoughts:
1.. Is this a Windows Forms or ASP.NET application. The former does not really need a connection pooling mechanism as the entire app might be able to share a common connection.
2.. If it''s ASP.NET, then it makes little sense to code, debug, test and deploy your own pooler. The code built into ADO.NET''s .NET data providers should work fine.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker''s Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker''s Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

<cr****@yahoo.comwrote in message news:11**********************@f1g2000cwa.googlegro ups.com...

Hello All，


前段时间，我实现了一个包含简单<的数据访问层br />
连接池。当时，我自己完成了这一切：我创建了N

连接，每个连接与一个工作线程关联，这将执行db命令。池已修复，并且在实例化db访问类时创建了所有连接

。

连接在整个执行过程中保持打开状态。如果此时没有连接

，则请求将被阻止，直到释放一个

连接。


In我的方法，我没有使用SQL服务器连接轮询（我实际上在我的连接字符串中禁用了
）。


这是使用ado在c ++中完成的。现在，我处于需要在.NET中实现db访问层的
的位置。我想知道我可以从.NET获得什么样的支持。


我想实现类似的模型。如果你能够对我的方法和在

ado.net上完成类似工作的方法有所影响，我将不胜感激。我也非常感谢您对该方法本身的评论。


谢谢

CD

Hello All,

Some time ago, I implemented a data access layer that included a simple
connectin pool. At the time, I did it all by myself: I created N
connections, each connection associated with a worker thread that would
execute the db commands. The pool was fixed and all the connections
were created when the db access class was instantiated. The
connections remained opened during the whole execution. If a connection
was not available at the moment, then the request would block until one
connection was freed.

In my approach, I did not use the SQL server connection polling (I
actually disabled it in my connection strings).

That was done in c++ using ado. Now, I am in a position where I need to
implement a db access layer in .NET. I was wondering what kind of
support I can get from .NET.

I want to implement a similar model. I would appreciate if you could
weigh on my approach and on the ways to accomplish something similar in
ado.net. I would also appreciate your comments on the approach itself.

Thank you
CD

CRB，


在我的想法中，你对连接池的解释比我们还要好吗，

你从应用程序的角度看到它。这里的常客主要是以数据库处理所需的所有连接的方式来考虑
。 AFAIK

设置为100，因此通常足够公平，可以在所有连接打开的同时处理所有应用程序

（这甚至可以更多

运行ASPNet应用程序）。因此，建议尽可能多地关闭
（换句话说，所有时间都不需要，而且在计算机时间断开连接的情况下，这是长期的b
你的联系。


我希望我能回答你的问题。


Cor

< ; cr **** @ yahoo.comschreef在bericht

新闻：11 ********************** @ f1g2000cwa.googlegro ups .com ...

CRB,

In my idea do you have another interpretation of Connection pooling than we,
you see it from your side of the application. Mostly by regulars here is
thought in the way the database is handling all connections it needs. AFAIK
is that set to 100, therefore often fair enough to handle all applications
in the same time as all connections are open (this can even be with more
ASPNet applications running). Therefore is than the advice to close as often
as possible (in other words all the time that it is not needed and that is
long in a disconnected situation in computer time) your connection.

I hope I answer your question a little bit with this.

Cor
<cr****@yahoo.comschreef in bericht
news:11**********************@f1g2000cwa.googlegro ups.com...

Hello All，


前段时间，我实现了一个包含简单的数据访问层

连接池。当时，我自己完成了这一切：我创建了N

连接，每个连接与一个工作线程关联，这将执行db命令。池已修复，并且在实例化db访问类时创建了所有连接

。

连接在整个执行过程中保持打开状态。如果此时没有连接

，则请求将被阻止，直到释放一个

连接。


In我的方法，我没有使用SQL服务器连接轮询（我实际上在我的连接字符串中禁用了
）。


这是使用ado在c ++中完成的。现在，我处于需要在.NET中实现db访问层的
的位置。我想知道我可以从.NET获得什么样的支持。


我想实现类似的模型。如果你能够对我的方法和在

ado.net上完成类似工作的方法有所影响，我将不胜感激。我也很感谢你对这个方法本身的评论。


谢谢

CD

Hello All,

Some time ago, I implemented a data access layer that included a simple
connectin pool. At the time, I did it all by myself: I created N
connections, each connection associated with a worker thread that would
execute the db commands. The pool was fixed and all the connections
were created when the db access class was instantiated. The
connections remained opened during the whole execution. If a connection
was not available at the moment, then the request would block until one
connection was freed.

In my approach, I did not use the SQL server connection polling (I
actually disabled it in my connection strings).

That was done in c++ using ado. Now, I am in a position where I need to
implement a db access layer in .NET. I was wondering what kind of
support I can get from .NET.

I want to implement a similar model. I would appreciate if you could
weigh on my approach and on the ways to accomplish something similar in
ado.net. I would also appreciate your comments on the approach itself.

Thank you
CD

你好Cor，


感谢您的留言。


你是对的。我正在考虑在客户端连接汇总

方。我知道，并在服务器上使用了连接池，如下所述。在我的情况下，例如，我打开10

连接，并且所有这些连接在

应用程序的生命周期内保持打开状态。它们中的每一个都与一个工作线程相关联，该线程执行客户端的数据库操作。


我知道这与我在听到的内容非常不同一般。为每笔交易打开一个

的连接，并尽快关闭。

尽管如此，我还是有一些动机来实现这个汇集在客户端

方：


- 安装SQL服务器的机器与其他

应用程序共享，我发现通过控制

客户端吞吐量。

- 当达到

池的最大大小时，我不希望我的交易失败。当使用池中的所有连接时，这是典型的行为。

- 在某些情况下（例如，当发生故障转移时）我最终得到了

" bogus"连接在游泳池中重新列入。当这些连接被重新使用时，应用程序就会卡住并且必须重新启动。

我当时消除这个问题的唯一方法是禁用

服务器上的连接池。

- 通过打开所有连接，我想我会节省一些

的时间。


这是我的经历。我并不是说这就是我想要再次做的事情，或者这些动机是否仍然适用于.NET。我的应用程序的

性质可能与众不同：它是一个

的实时视频应用程序。


性质我现在要实现的应用程序是使用.NET的典型

客户端服务器（没有ASP，没有WEB服务，没有Internet，

本地网络中的所有内容）。由于预计的用户负荷（自动和人），服务器将非常繁忙。


你能否看到我的方法有利的任何情况？

我只是让事情变得更复杂吗？

我是否因为保持连接打开而获得任何收益？

我原来的激励是否有效？


非常感谢您的投入。


亲切的问候

Cassiano

Cor Ligthert [MVP]写道：

Hello Cor,

Thank you for your message.

You are correct. I am considering connection pooling at the client
side. I am aware of,and have used connection pooliing at the server
level as you describe below. In my case, for example, I open 10
connections and all of them remain open for the life of the
application. Each of them associated with a worker thread that executes
the client''s db operations.

I know that this is very different from what I hear in general. Open a
connection for every transaction and close as soon as possible.
Nevertheless, I had some motivations for implementing this pooling at
the client side:

- The machine where SQL server was installed was shared with other
applications and I found easier to tune the cpu load by controlling the
throughput at the client side.
- I did not want my transaction to fail when the maximum size of the
pool was reached. This is the typical behavior when all connections in
the pool were being used.
- In some cases (when a failover occured, for example) I ended up with
"bogus" connections reenlisted in the pool. When those connections were
reused, the application would simply get stuck and had to be restarted.
The only way for me to eliminate this problem back then was to disable
connecion pooling at the server.
- By having all connections already open, I thought I would save some
time.

This was my experience. I am not saying that this is what I would like
to do again or whether these motivations are still valid with .NET. The
nature of my application might have been the difference: It was a
real-time video application.

The nature of the application that I want to implement now is a typical
client-server using .NET (no ASP, no WEB services, no Internet,
everything in the local network). The server will be very busy due to
the projected load of users (automatic and people).

Can you see any situation where my approach would be benefitial?
Am I just making things more complicated for me?
Do I gain anything for keeping the connections open?
Are my original motivatinons valid?

Your input is greatly appreciated.

Kind regards
Cassiano
Cor Ligthert [MVP] wrote:

CRB，


在我的想法中，你对连接池有另一种解释吗？ ，

你从应用程序的角度看到它。这里的常客主要是以数据库处理所需的所有连接的方式来考虑
。 AFAIK

设置为100，因此通常足够公平，可以在所有连接打开的同时处理所有应用程序

（这甚至可以更多

运行ASPNet应用程序）。因此，建议尽可能多地关闭
（换句话说，所有时间都不需要，而且在计算机时间断开连接的情况下，这是长期的b
你的联系。


我希望我能回答你的问题。


Cor


< cr **** @ yahoo.comschreef在bericht

新闻：11 ********************** @ f1g2000cwa.googlegro ups.com ...

CRB,

In my idea do you have another interpretation of Connection pooling than we,
you see it from your side of the application. Mostly by regulars here is
thought in the way the database is handling all connections it needs. AFAIK
is that set to 100, therefore often fair enough to handle all applications
in the same time as all connections are open (this can even be with more
ASPNet applications running). Therefore is than the advice to close as often
as possible (in other words all the time that it is not needed and that is
long in a disconnected situation in computer time) your connection.

I hope I answer your question a little bit with this.

Cor
<cr****@yahoo.comschreef in bericht
news:11**********************@f1g2000cwa.googlegro ups.com...

Hello All，


前段时间，我实现了一个数据访问层其中包括一个简单的

连接池。当时，我自己完成了这一切：我创建了N

连接，每个连接与一个工作线程关联，这将执行db命令。池已修复，并且在实例化db访问类时创建了所有连接

。

连接在整个执行过程中保持打开状态。如果此时没有连接

，则请求将被阻止，直到释放一个

连接。


In我的方法，我没有使用SQL服务器连接轮询（我实际上在我的连接字符串中禁用了
）。


这是使用ado在c ++中完成的。现在，我处于需要在.NET中实现db访问层的
的位置。我想知道我可以从.NET获得什么样的支持。


我想实现类似的模型。如果你能够对我的方法和在

ado.net上完成类似工作的方法有所影响，我将不胜感激。我也很感谢您对该方法本身的评论。


谢谢

CD

Hello All,

Some time ago, I implemented a data access layer that included a simple
connectin pool. At the time, I did it all by myself: I created N
connections, each connection associated with a worker thread that would
execute the db commands. The pool was fixed and all the connections
were created when the db access class was instantiated. The
connections remained opened during the whole execution. If a connection
was not available at the moment, then the request would block until one
connection was freed.

In my approach, I did not use the SQL server connection polling (I
actually disabled it in my connection strings).

That was done in c++ using ado. Now, I am in a position where I need to
implement a db access layer in .NET. I was wondering what kind of
support I can get from .NET.

I want to implement a similar model. I would appreciate if you could
weigh on my approach and on the ways to accomplish something similar in
ado.net. I would also appreciate your comments on the approach itself.

Thank you
CD

这篇关于连接池问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！