sqlcommand执行sql语句的效率问题

hthunter 2018-09-10 11:21:17
场景:SQL SERVER数据库,在500万行数据左右的一个表里更新和删除数据
当前使用方案:
1、插入数据使用SqlBulkCopy,非常快,插1000条数据一般只要一二十毫秒。
2、更新/删除方案一:每一条数据都拼成update / delete table_name ... where ..的语句用sqlcommand执行,这种效率非常低,已弃用
3、更新/删除方案二:多条数据的更新拼成以下语句然后用sqlcommand执行(因为执行长度受8000的限制,一次最多也就更新几十到一百多行数据,一千多行数据要执行十多次):
update a
set a.f_2 = b.f_2
from (
select 'x' as f_1, 'y' as f_2
union select 'a', 'b'
union select 'c', 'd'
......
)b
where a.f_1 = b.f_1
这样更新数据会比方案一快很多,删除数据也是类似处理,但是更新几百条和删除几百条数据一共一千多行也还是要2500毫秒左右(注意,是在500万数据里更新一千多行,如果表的数据少,肯定不会这么慢)。索引方案确定没改善的空间,分析过所有更新和删除用的条件都使用了索引进行检索。

有哪位大牛有更好的解决方案,望不吝赐教(本人c#和sql server都是半桶水,哈哈)
...全文
1085 34 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
34 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_43178679 2018-09-11
  • 打赏
  • 举报
回复
SQL不完善,维护性比较差,另外用其他的吧。
liulilittle 2018-09-11
  • 打赏
  • 举报
回复
把你要更新的数据,批量插入到一张 tmp table 内,但是建议增加一个标志的唯一索引可以提高查询效率 然后 update 欲更新的表 as x join tmp_table as y where x.id = y.id;
闭包客 2018-09-11
  • 打赏
  • 举报
回复
即使用了临时表这种方法,速度还是比较慢不说,还会带来数据一致性的问题。
qq_43177545 2018-09-11
  • 打赏
  • 举报
回复
如果可以的话,建议你用这个:https://github.com/StackExchange/Dapper
我是小数位 2018-09-11
  • 打赏
  • 举报
回复
我8000多万的表更新也很快啊,先把要更新的数据放到临时表,可以用SqlBulkCopy插入到临时表,用聚集索引关联更新(保证是唯一的),临时表也建立索引,存在的更新,不存在的新增
游北亮 2018-09-11
  • 打赏
  • 举报
回复
我以前是先SELECT出需要更新的记录主键, 再用主键进行更新, 避免用索引更新
xuzuning 2018-09-10
  • 打赏
  • 举报
回复
你都知道插入表会很快,那么你何不先将数据插入临时表后在实行关联操作
这样不就突破了 8000 的限制了吗?
hthunter 2018-09-10
  • 打赏
  • 举报
回复
无外键

引用 6 楼 hthunter 的回复:
[quote=引用 5 楼 zhongchengli 的回复:]
可以试试下面的方法
1.去除所有(更新和删除用的条件都使用了索引)以外的索引,外键等
2.用储存程序 (Stored Procedure)

无多余索引,调用存储过程跟直接执行sql语句应该效率不会有太大差距吧,不过有空还是试试[/quote]
hthunter 2018-09-10
  • 打赏
  • 举报
回复
引用 5 楼 zhongchengli 的回复:
可以试试下面的方法
1.去除所有(更新和删除用的条件都使用了索引)以外的索引,外键等
2.用储存程序 (Stored Procedure)

无多余索引,调用存储过程跟直接执行sql语句应该效率不会有太大差距吧,不过有空还是试试
zhongchengli 2018-09-10
  • 打赏
  • 举报
回复
可以试试下面的方法 1.去除所有(更新和删除用的条件都使用了索引)以外的索引,外键等 2.用储存程序 (Stored Procedure)
hthunter 2018-09-10
  • 打赏
  • 举报
回复
关键是有些数据不能使用多线程并行处理,否则的话多线程肯定会有提升
hthunter 2018-09-10
  • 打赏
  • 举报
回复
发这里是想问sqlcommand本身有没有一些机制或者参数设置可以提高效率,sql语句就是用分析工具分析过了,觉得sql语句方面改善空间不大
  • 打赏
  • 举报
回复
你这个应该发sql板块去,而且你这种问题最好通过sql的分析工具具体分析
hthunter 2018-09-10
  • 打赏
  • 举报
回复
好多年没发过贴了,现在竟然可以发400分的贴了
newnazi 2018-09-10
  • 打赏
  • 举报
回复
1) update 语句都命中索引 确实不太现实的想法 当然索引要合理

2)大的操作最好放到存储过程中

3)最简单的方式就是提高硬件性能,还有 生产DB 频繁update 本身效率就很低

4)可以使用临时表

闭包客 2018-09-10
  • 打赏
  • 举报
回复
引用 15 楼 hthunter 的回复:
[quote=引用 13 楼 closurer 的回复:]
问题的关键在于你更新/删除的数据,有什么统一的条件?如果这个条件是命中聚集索引,那是最快的。

你找不到更新/删除的数据的规律,靠拼接 SQL 去处理,是没有优化空间的。

上面说了,条件都是命中索引的[/quote]

你那种拼接 SQL 语句,每个语句都命中索引……是很糟糕的做法。

你要知道你更新的数据集,有什么共同规律,才知道怎么设计你的索引。
hthunter 2018-09-10
  • 打赏
  • 举报
回复
500万已经是我们的日常库了,中间还有不少业务逻辑的判断,历史库是亿级的

再次谢谢各位的帮助
hthunter 2018-09-10
  • 打赏
  • 举报
回复
经过测试,前面两位朋友建议的先插入临时表,然后再关联更新的方式,效率有了较大提高,把原来总的多次执行的缩减了很多步骤,时间也从总的2500ms降到500ms,这已经能基本达到我的期望值了,谢谢各位的热心帮助。呆会儿结贴
hthunter 2018-09-10
  • 打赏
  • 举报
回复
引用 20 楼 hanjun0612 的回复:
但是如果你update能命中索引的话,那么就另说


重建或者修改索引也是很耗时的,并且我这个操作也是很频繁,否则我也不会纠结这两秒多钟了
正怒月神 2018-09-10
  • 打赏
  • 举报
回复
但是如果你update能命中索引的话,那么就另说
加载更多回复(11)

111,097

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧