SQL Server 2008 - 奇怪的慢查询
我的数据库中有一个大表（数百万条记录，大小约为 500GB）。
在这个表中，我有很多列，其中有一个名为 FinishTime 的日期字段。 该字段有一个索引。
现在问题是：
当我运行一个带有条件 FinishTime >= 的简单选择查询时 - 查询需要很长时间才能完成。
当我使用条件 FinishTime >= 运行简单的选择查询时，查询会立即完成。
奇怪的是，第二个查询的输出应该包含第一个查询的所有输出，甚至更多。
更多信息：我使用 MS JDBC 驱动程序通过 JAVA 应用程序运行这些查询。我正在使用PreparedStatement。
运行两个查询的代码是相同的。
有什么想法吗？
谢谢
编辑：更多信息和更正： 我还有一个相关字段 - JobId，整数，它也有一个索引。 问题似乎发生在这些查询上（我在管理工作室中看到卡住的查询，由于 PAGEIOLATCH_SH）：
卡住的查询：
select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 09:23:00'}) AND ([JobId]=5)
立即完成的查询：
select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 04:23:00'}) AND ([JobId]=5)
I have a big table in a database (millions of records, size is ~500GB).
In this table I have many columns, among them there is a date field called FinishTime.
There is an index on that field.
Now to the problem:
When I run a simple select query with the condition FinishTime >= <now - 10 hours> - query takes a lot of time to complete.
When I run a simple select query with the condition FinishTime >= <now - 11 hours> - query completes immediately.
The weird thing is that the second query's output should contain all of the first query's output, and more.
Some more information: I'm running these queries through a JAVA application, using the MS JDBC driver. I'm using a PreparedStatement.
The code that runs both queries is the same.
Any ideas?
Thanks
EDIT: More information, and corrections:
I have one more relevant field - JobId, integer, which also have an index.
The problem seem to happen on those queries (I see the stuck query in the management studio, due to PAGEIOLATCH_SH):
Stuck query:
select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 09:23:00'}) AND ([JobId]=5)
Query that completes immediately :
select * from jobsData (with readuncommitted) where ([FinishTime >= {ts '2011-05-19 04:23:00'}) AND ([JobId]=5)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于它是一个准备好的语句，它可能针对传入的第一个参数优化了查询（我猜是 11 小时的减法）。
这可以通过执行查询并带有强制重新编译查询计划的提示来测试。
请参阅 - http://msdn.microsoft.com/en-us/library/ms181714 .aspx
这不是一个漂亮的解决方案，但如果这提高了性能，您知道它与此相关，您可以考虑添加查询提示，或者是否仅传递有限范围的值，为每个值提供一个语句变体。
编辑 - 从您上面的评论来看，它们都立即在管理工作室中运行，这似乎强化了我对此的感觉。
查询计划缓存是由 sqlserver 而不是 java 应用程序完成的，
将您的语句传递给 sqlserver，通知它变量位置，并让它在第一次运行时基于它生成可重用的查询计划。我怀疑（但如果不搜索文档就无法确定）调用它会强制构建新的查询计划，因为这会抵消它的大部分好处。
不要对查询进行参数化，而是尝试将查询构建为字符串并执行它。
As its a prepared statement its probably optimised the query for whatever the first parameter passed in was (I'd guess the 11 hour subtraction)
This can be tested by executing the query with a hint to force recompilation of the query plan.
See - http://msdn.microsoft.com/en-us/library/ms181714.aspx
Its not a pretty solution but if this improves performance, you know its related to this and you can look at adding query hints, or if only a limited range of values will ever be passed in having a statement for each variant.
Edit - From your comment above that says they both run immediately in management studio, it seems to reinforce my feeling on this.
Query plan caching is done by the sqlserver not the java app,
Passes your statement across to the sqlserver, notifies it of the variable places and lets it generate a reusable query plan based upon it when its first run. I doubt (but can't be certain without hunting through docs) that calling it forces a new query plan to be build as that would negate a large part of its benefits.
Instead of parametrising the query try building the query as a string and executing it.