sql：如何改进这个语句
我有以下 sql 语句，我需要加快速度。 有 500k 行，并且我有一个“HARDWARE_ID”索引，但这仍然需要一秒钟的时间才能执行。
有人有什么想法吗？
select
*
from
DEVICE_MONITOR DM
where
DM.DM_ID = (
select
max(DM_ID)
from
DEVICE_MONITOR
where
HARDWARE_ID=#value#
)
我发现以下索引也很有帮助...
CREATE INDEX DM_IX4 ON DEVICE_MONITOR (DM_ID, HARDWARE_ID);
在我的测试中，它将运行时间从 26 秒减少到 20 秒。
感谢您的帮助。
I have the following sql statement that I need to make quicker.
There are 500k rows, and I an index for 'HARDWARE_ID', but this still take up to a second to perform.
Does anyone have any ideas?
select
*
from
DEVICE_MONITOR DM
where
DM.DM_ID = (
select
max(DM_ID)
from
DEVICE_MONITOR
where
HARDWARE_ID=#value#
)
I've found the following index is also a great help...
CREATE INDEX DM_IX4 ON DEVICE_MONITOR (DM_ID, HARDWARE_ID);
In my test it drops the runtime from 26seconds to 20 seconds.
Thanks for all your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果 DM_ID 上有聚集索引，那么这看起来是最快的查询。
编辑：确认。丹尼尔有正确答案。错过了。
If you have a clustered index on DM_ID, then that looks like the fastest query.
Edit: Ack. Daniel has the correct answer. Missed that.
DM_ID 的索引应创建为 asc
问题可能在于您发现 HARDWARE_ID 的匹配速度非常快，但随后必须对这些记录进行排序才能从中获取最大值，并且此操作非常耗时。
尝试比较以下语句：
查询 1 是问题所在，因为您将看到，
如果创建索引，查询 2 的工作速度会更快，而查询的工作速度仍然比您可能更新的统计信息慢。但其他查询可能只会运行得更慢。
如果可能，而不是 * 仅使用您真正需要的列
The index for the DM_ID should be created as asc
The problem might be in this that You found very fast match form HARDWARE_ID, but then those records have to sorted to fetch max from them and this operation is time consuming.
Try to compare this statements:
The query 1 is the problem, as you shall see that the 2 is working faster
if the index is created, and the query still works slowly than, you may update the statistics. But other queries will probably work only slower.
If is possible instead of * use only column that You really need
考虑将“*”更改为仅您需要的属性列表。通常这可以显着提高速度。
Consider changing '*' to only list of attributes you need. Very often this can give you substantial increase in speed.