经典 ASP - 使用一个连接进行多个查询？
考虑一个在 IIS6 上运行且具有专用 SQL Server 2008 后端的经典 ASP 站点...
场景 1：
打开连接
通过 ASP 页面进行 15 次查询、更新等
关闭连接
场景 2：
对于每个查询、更新等，打开和关闭连接
使用连接池，我认为场景 2 是最有效和可扩展的。
我的这个假设是正确的吗？
编辑：更多信息
这是数据库操作分布在单独函数中的大量 asp 代码中，执行单独的操作等。它不是快速连续完成的 15 个查询。想象一个具有许多功能的大网站，包括等等。
Consider a classic ASP site running on IIS6 with a dedicated SQL Server 2008 backend...
Scenario 1:
Open Connection
Do 15 queries, updates etc all through the ASP-page
Close Connection
Scenario 2:
For each query, update etc, open and close the connection
With connection pooling, my money would be on scenario 2 being the most effective and scalable.
Would I be correct in that assumption?
Edit: More information
This is database operations spread over a lot of asp-code in separate functions, doing separate things etc. It is not 15 queries done in rapid succession. Think a big site with many functions, includes etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
从根本上来说，ASP 页面是同步的。那么为什么不在每次页面加载时打开一次连接，并在每次页面加载时关闭一次连接呢？所有其他打开/关闭似乎都是不必要的。
Fundamentally, ASP pages are synchronous. So why not open a connection once per page load, and close it once per page load? All other opens/closes seem to be unnecessary.
如果我理解正确，您正在考虑在各种包含的各种函数中保存的复杂代码之间共享连接对象。
在这种情况下，这将是一个坏主意。如果其他代码可能发现需要修改它们，则很难保证连接上的正确状态和设置。此外，您有时可能会有获取 Firehose 记录集的代码，但当调用另一段也需要连接的代码时，该代码尚未完成处理。在这种情况下，您无法共享连接。
让每个原子代码块获得自己的连接会更好。连接将处于干净的已知状态。必要时多个连接可以并行运行。正如其他人指出的那样，底层连接池几乎完全减轻了连接创建的成本。
If I understand you correctly you are considering sharing a connection object across complex code held in various functions in various includes.
In such a scenario this would be a bad idea. It becomes difficult to guarantee the correct state and settings on the connection if other code may have seen the need to modify them. Also you may at times have code that fetches a firehose recordset and hasn't finished processing when another piece of code is invoked that also needs a connection. In such a case you could not share a connection.
Having each atomic chunk of code acquire its own connection would be better. The connection would be in a clean known state. Multiple connections when necessary can operate in parrallel. As others have pointed out the cost of connection creation is almost entirely mitigated by the underlying connection pooling.
在您的场景 2 中，您的应用程序和 SQLServer 之间存在一个往返执行每个查询的过程，这会消耗您服务器的资源，并且总执行时间将会增加。
但在场景 1 中，只有一次往返，并且 SQLServer 将一次性运行所有查询。所以它更快，资源消耗更少
编辑：好吧，我以为你的意思是一次多个查询..
因此，启用连接池后，每次事务后关闭连接完全没有问题。所以选择场景2
in your Scenario 2, there is a round-trip between your application and SQLServer for executing each query which consumes your server's resources and time of total executions will raise.
but in Scenario 1, there is only one round-trip and also SQLServer will run all of the queries in just one time. so it is faster and less resource-consuming
EDIT: well, I thought you mean multiple queries in one time..
so, with connection pooling enabled, there is exactly no problem in closing connection after each transaction. so go with Scenario 2
最佳做法是打开连接一次，读取所有数据并尽快关闭连接。关闭连接后，您可以对检索到的数据执行您喜欢的操作。在这种情况下，您不会打开太多连接，也不会打开连接太长时间。
即使您的代码在多个位置有数据库调用，创建连接的开销也会使事情变得比等待更糟糕 - 除非您说您的页面需要很多秒才能在服务器端创建？通常，即使没有受控的数据访问并且具有许多功能，您的页面也应该在不到一秒的时间内在服务器上生成。
Best practice is to open the connection once, read all your data and close the connection as soon as possible. AFTER you've closed the connection, you can do what you like with the data you retrieved. In this scenario, you don't open too many connections and you don't open the connection for too long.
Even though your code has database calls in several places, the overhead of creating the connection will make things worse than waiting - unless you're saying your page takes many seconds to create on the server side? Usually, even without controlled data access and with many functions, your page should be well under a second to generate on the server.
我相信默认连接池大约有 20 个连接，但 SQLServer 可以处理更多。从服务器获取连接需要最长的时间（假设您没有使用命令执行任何愚蠢的操作），因此我认为每页获取一个连接并在之后使用时终止它没有任何问题。
为了可扩展性，您可能会遇到这样的问题：连接池变得太繁忙并且超时，而您的脚本等待连接可用，而您的数据库有 100 个空闲连接，但没有人使用它们。
在同一页面上创建和删除得到了我的投票。
I believe the default connection pool is about 20 connections but SQLServer can handle alot more. Getting a connection from the server takes the longest time (assuming you are not doing anything daft with your commands) so I see nothing wrong with getting a connection per page and killing it if used afterwards.
For scalability you could run into problems where your connection pool gets too busy and time outs while your script waits for a connection to be come available while your DB is sat there with a 100 spare connections but no one using them.
Create and kill on the same page gets my vote.
从性能角度来看，没有显着差异。 ADODB 连接池管理与数据库的实际连接。 Adodb.connection .open 和 .close 只是连接池的一个外观。实例化 1 或 15 个 adodb.connection 对象对于性能来说并不重要。在使用事务之前，我们将连接字符串与 adodb.command (.activeConnection) 结合使用，并且从未显式打开或关闭连接。
显式保留对 adodb.connection 的引用的原因是事务或基于连接的函数，例如 mysql last_inserted_id()。在这些情况下，您必须绝对确定每个查询都获得相同的连接。
From a performance point of view there is no notable difference. ADODB connection pooling manages the actual connections with the db. Adodb.connection .open and .close are just a façade to the connection pool. Instantiating either 1 or 15 adodb.connection objects doesn't really matter performance wise. Before we where using transactions we used the connection string in combination with adodb.command (.activeConnection) and never opened or closed connections explicitly.
Reasons to explicitly keep reference to a adodb.connection are transactions or connection-based functions like mysql last_inserted_id(). In these cases you must be absolutely certain that you are getting the same connection for every query.