优化 SQL 连接性能？
这不是一个关于优化 SQL 命令的问题。 我想知道有什么方法可以确保 SQL 连接保持打开状态并准备好尽可能有效地处理命令。
我现在看到的是我可以执行一个 SQL 命令，该命令将花费约 1 秒，额外的执行将花费约 300 毫秒。 这是在之前针对 SQL 服务器（从另一个应用程序实例）执行该命令之后...因此，在该应用程序初始执行之前，应为执行的查询完全填充 SQL 缓存。 只要我不断地重新执行查询，我就会看到大约 300 毫秒的时间，但如果我让应用程序空闲 5-10 分钟并返回，下一个请求将恢复到约 1 秒（与初始请求相同）。
有没有办法通过连接字符串或 SqlConnection 上的某些属性指导框架保持连接水合并准备好有效处理查询？
This is not a question about optimizing a SQL command. I'm wondering what ways there are to ensure that a SQL connection is kept open and ready to handle a command as efficiently as possible.
What I'm seeing right now is I can execute a SQL command and that command will take ~1s, additional executions will take ~300ms. This is after the command has previously been executed against the SQL server (from another application instance)... so the SQL cache should be fully populated for the executed query prior to this applications initial execution. As long as I continually re-execute the query I see times of about 300ms, but if I leave the application idle for 5-10 minutes and return the next request will be back to ~1s (same as the initial request).
Is there a way to via the connection string or some property on the SqlConnection direct the framework to keep the connection hydrated and ready to efficiently handle queries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您检查过程序的执行计划吗？ 我相信执行计划会加载到服务器上的内存中，然后在一定时间段后或根据过程中访问的表等而被清除。 我们遇到过简化存储过程（可能拆分它们）减少数据库服务器在计算计划时必须完成的工作量...并最终减少第一次调用过程的时间...您可以发出命令强制存储过程每次重新编译以测试是否减少了初始调用时间...
我们遇到过这样的情况：存储过程的复杂性使得数据库服务器必须根据不同的参数不断地重新编译，这大大减慢了速度，将 SP 拆分或将大型选择语句简化为多个更新语句等有很大帮助。
其他想法可能是间歇性地调用一个简单的 getDate() 或类似的方法，以便 sql 服务器处于唤醒状态（希望这是有道理的）...与将 asp.net 应用程序保留在 IIS 的内存中非常相似。
Have you checked the execution plan for your procedures. Execution plans I believe are loaded into memory on the Server and then get cleared after certain periods of time or depending on what tables etc are accessed in the procedures. We've had cases where simplifying stored procedures (perhaps splitting them) reduces the amount of work the database server has to do in calculating the plans...and ultimately reduces the first time the procedure is called...You can issue commands to force stored procedures to recompile each time for testing whether you are reducing the initial call time...
We've had cases where the complexity of a stored procedure made the database server continually have to recompile based on different parameters which drastically slowed it down, splitting the SP or simplifying large select statements into multiple update statements etc helped a considerable amount.
other ideas are perhaps intermittently calling a simple getDate() or similar every so often so that the sql server is awake (hope that makes sense)...much the same as keeping an asp.net app in memory in IIS.
.NET 连接池中打开的连接的默认值为零。
您可以在连接字符串中将此值调整为 1 或更大：
查看有关这些内容的更多信息 MSDN 中的选项。
The default value for open connections in a .NET connection pool is zero.
You can adjust this value in your connection string to 1 or more:
See more about these options in MSDN.
您可以通过不关闭它来使其保持打开状态。 :) 但这是不建议的，因为连接池将为您处理连接管理。 你启用了吗？
you keep it open by not closing it. :) but that's not adviseable since connection pooling will handle connection management for you. do you have it enabled?
默认情况下，ADO .NET 中启用连接池。 这将通过应用程序使用的连接字符串进行。 详细信息请参见在 SQL Server 中使用连接池
by default the connection pooling is enabled in ADO .NET. this will be through the connection string used by the application. More info in Using Connection Pooling with SQL Server
如果您使用多个数据库连接，可能会更有效。 拥有一个数据库连接意味着最佳访问速度始终会受到顺序限制。 而拥有 >1 个连接意味着编译器有机会进一步优化并发访问。 我猜你正在使用.NET？
此外，如果您重复发出相同的 SQL 语句，您的数据库服务器可能会在短时间内缓存结果，从而使结果集的返回更快。
If you use more than one database connection, it may be more efficent. Having one database connection means the best possible access speed is always going to be limited sequentially. Whereas having >1 connections means theres an opportunity there for your compiler to optimize concurrent access a little more. I guess you're using .NET?
Also if your issuing the same SQL statement repeatedly, its possible your database server is caching the result for a short period of time, therefore making the return of the resultset quicker..