传递查询的连接池问题 [英] Connection pooling issue with pass through queries
问题描述
我正在创建一个MS Access应用程序，它使用传递查询连接到SQL Server
2005数据库，并使用ADO调用存储的
过程。我运行了一条SQL跟踪并开始点击各种
表单来查看数据库中的事情处理方式。我注意到一个惊人数量的连接被打开了，就好像有绝对没有连接池的
。几次点击后，我有20个新的
连接。似乎每个传递查询都打开了一个新的连接
，即使连接字符串完全相同（每个都是相同的）（我在代码中将它们设置为全局）申请
启动）。我还注意到，如果我将连接字符串设置为使用
SQL Server登录（用户名/密码）而不是可信连接，
问题消失了，连接就是适当地重复使用。请注意，通过ADO对数据库进行的
调用没有出现此问题。
另外，我没有使用ODBC DSN。
我有没有理由看到这种行为？在某个地方，我忽略了改变设置吗？
比尔E.
好莱坞，佛罗里达州/>
I''m creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I''m not using an ODBC DSN.
Is there a reason why I''m seeing this behavior? Is there a setting
somewhere that I''ve neglected to change?
Bill E.
Hollywood, FL
推荐答案
Bill E.写道：
Bill E. wrote:
我正在创建一个MS Access应用程序连接到SQL Server
2005数据库使用传递查询和ADO来调用存储的
程序。我运行了一条SQL跟踪并开始点击各种
表单来查看数据库中的事情处理方式。我注意到一个惊人数量的连接被打开了，就好像有绝对没有连接池的
。几次点击后，我有20个新的
连接。似乎每个传递查询都打开了一个新的连接
，即使连接字符串完全相同（每个都是相同的）（我在代码中将它们设置为全局）申请
启动）。我还注意到，如果我将连接字符串设置为使用
SQL Server登录（用户名/密码）而不是可信连接，
问题消失了，连接就是适当地重复使用。请注意，通过ADO对数据库进行的
调用没有出现此问题。
另外，我没有使用ODBC DSN。
我有没有理由看到这种行为？在某个地方，我忽略了改变吗？
比尔E.
好莱坞，FL
I''m creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I''m not using an ODBC DSN.
Is there a reason why I''m seeing this behavior? Is there a setting
somewhere that I''ve neglected to change?
Bill E.
Hollywood, FL
你为什么担心它？你有没有一个问题，如果你没有使用SQL跟踪，你会注意到这个问题吗？
-
Rick Brandt ，Microsoft Access MVP
电子邮件（视情况而定）至...
在Hunter dot com的RBrandt
Why are you worried about it? Have you had a single problem that you would
have noticed had you NOT used SQL trace?
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Rick ，
这就像问我们为什么要打扰数据库
连接池。这是一个有效利用资源的问题，并且可以避免生产中可能出现的问题。我是一个单独的用户
点击一个单独的表格并打开超过20个连接。
这只是简单的草率和不可接受。谁知道在扩展会话中使用完整的
应用程序的许多用户将打开多少
连接。不久前我不得不修改别人的网络应用程序，因为它打开了数百个
连接，并阻止所有用户跟踪超过
允许连接的数量。
当我设置连接字符串使用
SQL Server时，问题就消除了登录而不是可信连接。直到我理解了这里发生了什么，我才会坚持下去。任何洞察
的原因都会受到欢迎。
谢谢，
Bill E. br $> b $ b好莱坞，FL
Rick,
That''s almost like asking why we should bother with database
connection pooling. It''s a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That''s just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn''t long ago that I had to
fix someone else''s web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.
The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what''s going on here, I''m going to stick with that. Any insight into
what may be causing this would be welcomed.
Thanks,
Bill E.
Hollywood, FL
Bill E.写道：
Bill E. wrote:
Rick，
这就像问我们为什么要打扰数据库
连接池。这是一个有效利用资源的问题，并且可以避免生产中可能出现的问题。我是一个单独的用户
点击一个单独的表格并打开超过20个连接。
这只是简单的草率和不可接受。谁知道在扩展会话中使用完整的
应用程序的许多用户将打开多少
连接。不久前我不得不修改别人的网络应用程序，因为它打开了数百个
连接，并阻止所有用户跟踪超过
允许连接的数量。
当我设置连接字符串使用
SQL Server时，问题就消除了登录而不是可信连接。直到我理解了这里发生了什么，我才会坚持下去。任何洞察
可能导致这种情况都会受到欢迎。
Rick,
That''s almost like asking why we should bother with database
connection pooling. It''s a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That''s just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn''t long ago that I had to
fix someone else''s web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.
The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what''s going on here, I''m going to stick with that. Any insight into
what may be causing this would be welcomed.
我只能告诉你，我一直在使用带有可信连接的ODBC
，大约有200个用户超过十年而且从未有过有一个问题
用它。
连接被重复使用。
-
Rick Brandt，Microsoft Access MVP
电子邮件（视情况而定）至...
在Hunter dot com的RBrandt
I can only tell you that I have been using ODBC with trusted connections
with around 200 users for over a decade and have never once had an issue
with it.
Connections ARE re-used.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
这篇关于传递查询的连接池问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
