更新语句运行时间过长与否 [英] Update statement running for too long or not

问题描述

我刚开始处理这么多数据(2000 万行)，我不知道就查询持续时间而言我应该期待什么:

I'm new to working with this much data (20 million rows) and I don't know what I should expect as far as query duration:

update table set field = '1234'  

场上没有索引.这句话用了 25 分钟.数据库设置为简单恢复.25 分钟是不是太长了?表有 9 列，小数据类型 <50 varchar.

No index on field. This statement took 25 minutes. The database is set to Simple Recovery. Does 25 minutes seem too long? Table has 9 columns with small data types <50 varchar.

推荐答案

如果你在一个事务中更新了 2000 万行，那么你的时间完全由你的 IO 子系统驱动:你有什么样的驱动器，什么样的磁盘文件布局等等. 如果您在 raid 10 中有 40 个主轴，有 4 个平衡文件和一个单独的类似电池用于日志，那么结果会慢得令人担忧.如果您使用单个 MDF 进行测试，该 MDF 与单个消费级质量 5000rpm HDD 上的 LDF 共享主轴，那么您的时间会非常快.

IF you updated 20M rows in one single transaction, then your time was entirely driven by your IO subsystem: what kind of drives you have, what disk files layout etc. If you have 40 spindles in raid 10 with 4 balanced files and a separate similar battery for the log then the result is worryingly slow. If you tested this with one single MDF that shares the spindle with the LDF on a single consumer quality 5000rpm HDD then your time is amazingly fast.

这篇关于更新语句运行时间过长与否的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！