业务是这样的,定期要把最新的数据写到数据库里。表里有一列UpdateStatus,用来设置Archived和Current,每次在写入新数据时,把已有的数据改成Archived,然后新的数据改成Current。
所以每次插入数据的步骤是,
1. 先根据条件把表里对应的数据Update成Archived,
2. 然后Insert新数据
3. 再Update新插入的新数据为Current用的是批量插入(SqlBulkCopy),数据量少的时候性能还可以。由于需要频繁大批量Insert,所以表没有主键外键,索引加不加好像并没有太大改进。现在的问题是有的表里有1亿多条数据,其它表里也都是百万级别的,而且一次更需批量插入1千多次,每次Update几乎是都是遍历全表,最少也要4,5分钟,全部做完就要4多千分钟,这个是不能接受的。
有没有高人指点一下?
性能优化
所以每次插入数据的步骤是,
1. 先根据条件把表里对应的数据Update成Archived,
2. 然后Insert新数据
3. 再Update新插入的新数据为Current用的是批量插入(SqlBulkCopy),数据量少的时候性能还可以。由于需要频繁大批量Insert,所以表没有主键外键,索引加不加好像并没有太大改进。现在的问题是有的表里有1亿多条数据,其它表里也都是百万级别的,而且一次更需批量插入1千多次,每次Update几乎是都是遍历全表,最少也要4,5分钟,全部做完就要4多千分钟,这个是不能接受的。
有没有高人指点一下?
性能优化
而且一次更需批量插入1千多次
每次Update几乎是都是遍历全表按以下优先级依次做优化:
1.尝试将你的大数据表做分区或历史数据转移,并修改程序对应的查询和更新部分。
2.针对UPDATE语句设计最合适的索引/分区索引。
3.努力把更新的次数减少,比如先把待插入和更新数据合并到一张临时表中,再一次性将临时表插入或更新主表。
1. 先根据条件把表里对应的数据Update成Archived,
(应该是把非Archived的数据Update成Archived,字段加索引会快很多很多)2. 然后Insert新数据
3. 再Update新插入的新数据为Current
(直接INSERT,对应字段直接Current,根本不必两次操作,如果外部导入问题,可以考虑在外部数据写入的地方修改)
如果上面全部是,可考虑两张表,一张放Archived一张Current,字段就可以省了,也就没有这么多UPDATE操作,至多是搬迁操作。
搬迁操作实际就是将Current表数据写入Archived,然后清空Current表,在Current表写入新数据。如果时间日期比较规整的话,可以考虑两个表都按日期分区,每天作业将Current表前一天分区切换到Archived,这样连搬迁操作的费用都可以省掉了
谢谢
1. 现在基本每条数据两个版本,一个Archived一个Current,没有其他历史版本,一次全更新就有2千多万条新数据,也就是表最少也有将近4千万条数据,效率还是很低下。
2. 我把Update中Where的列建了非聚集索引,没有发现显著改变
3. 能不能详细说明?用的是SQL Server 2008,它会把查询的表保存到数据缓存,直到把内存占完,之后效率就更慢了。
有时候只是部分更新,所以不能把所有的非Archived改成Current,要根据待插入的数据中的条件去更新已有的数据。
直接插的时候就是Current这条可行,的确会减少很多时间。
你这个很有问题,如果只存两条,你应该做两次UPDATE,而不是一次DELETE,一次UPDATE和一次INSERT
你这个很有问题,如果只存两条,你应该做两次UPDATE,而不是一次DELETE,一次UPDATE和一次INSERT先把Archived的Delete掉
再插入新的数据标识为Pending
然后Update Current为Archived
最后UPdate Pending为Current
思路:
1:设置源数据表的行版本,查询元数据源表的插入数据,更新数据、已删除数据
2:将删除数据的记录同步到目标数据进行删除操作;对于需要插入的和更新的目标数据删除,然后将源数据表的插入和更新数据插入目标数据表中。微软自带的数据变更跟踪:http://www.cnblogs.com/worfdream/articles/3046377.html
1.表中根据ID分组,每个ID存两条数据,TYPE字段一条CURRENT一条ARCHIVED。
2.频繁根据ID将新数据导入这张表,导入的数据作为C,前一条C变成A,前一条A废弃。如果流程大致是这样,那只需要一个索引 ID,TYPE:
鉴于你现在的更新就已经是贼慢的,我就暂定你们对更新的实时性要求不高,我觉得这时的流程应该是这样的:
1.一条更新请求进来,将新数据的所有字段传入
2.根据新数据的ID,找到表中的C和A,将C的所有字段更新到A里
3.把新数据的所有字段更新到表中的A里,通过ID和TYPE查找
Sorry, 没太看懂,没有源数据表,是从外部的文本文件读进来的数据。
如: create nonclustered index ix_tableName_Archived on tableName (columns) where UpdateStatus='Archived'Update的时候使用筛选索引ix_tableName_Archived 。
如: create nonclustered index ix_tableName_Current on tableName (columns) where UpdateStatus='Current'Update的时候使用筛选索引ix_tableName_Current 。
不如这样,lz尝试把新数据放到一张单独的零时表中,然后用select进行查询得到需要更新的记录,然后一次性在原表中update掉数据,完成后在insert新记录到原表,同时delete零时表。
好处是批量处理可能要比单条记录逐条更新要快得多
UpdateStatus字段因为只有2种值:Archived和Current,建立索引未必能有效,不过感觉lz的意思是大部分都是Archived少部分是Current,那么加上条件UpdateStatus='Current'因为记录不是很多应该会很快,然后对这些记录做更新。
1.建立存储过程: 存储过程将每次插入的数据改为current状态
2.建立临时表用于存放 新插入的数据和需要修改为Active的数据
3.将active的数据插入到有1亿条数据的表中
A 表存 archived 数据
C 表存 current 数据
P 表存 Pending 数据1.新数据来放在p表,
2.truncate A表,C表数据Insert进A表,Insert 时同时设置状态为archived
3.truncate C表,P表数据Insert进C表,Insert 时同时设置状态为 current
4.truncate P表然后继续循环
按什么分区就要看业务逻辑了。
“ 1. 先根据条件把表里对应的数据”,除了你提到的这些,应该还有其它的列能把数据归类吧。
2. 贴出你的update语句和执行计划。再看要加什么样的索引。
3. 这样大批量的有损操作,还要注意日志的性能。
二:
3. 再Update新插入的新数据为Current
把你的这两个步骤并成一步,直接在插入的时候指字值为Current,而不是你当前的先插入别的内容,再进 行修改
二:对你这张1亿多的表进行分区,根据你实际业务来分,如果是一天插入一次的,可以按时间字段进行分区,然后开始你第一步的工作,"先根据条件把表里对应的数据Update成Archived",不同的是先把这一步份数据切出到一张新的表里,毕竟相对1亿多的表,这部份数据是少的,然后对新表进行更新Archived,更新完了之后再把数据切入到1亿多的表里。。分区切换只是几妙钟的事情。。楼主可以试试。。不正确之处,请多指点。
谢谢,关键是时间字段并不用于查询条件,按时间分区没有意义。还有关于合并Pending的那一步骤,LZ也曾经考虑过,但是感觉会影响数据的准确性。比如:
先把Current的改成Archived, 再去插入Current的,如果插入过程失败,则Current的数据就缺失了。
只有确保先插入完成,再去更新之前的数据,才比较稳妥。
分区是为了提升查询效率,但是前提是不同的分区在不同的磁盘上,才可减少I/O耗时。
貌似并不是分区越多越好,这样的话1亿条数据做3,4个分区性能又能提升多少?而且默认都是放在PRIMARY这个文件组下。是否有必要为每个分区创建新的文件组?
楼主可以考虑一下这位伙计的方案吧。有些时候技术不能满足需求可能是因为业务分析处理不够合理吧。
这样做的好处就是对你那个亿级的表只做Insert处理,而另外一张表C是作为一张缓存表,照你讲的需求至多也就1000条数据吧。每次将这个表的数据插入到那个亿级的表后,就删除对应的数据。因为数据量少,所以感觉就算频繁做检索或者删除处理压力应该不会太大。
楼主可以考虑一下这位伙计的方案吧。有些时候技术不能满足需求可能是因为业务分析处理不够合理吧。
这样做的好处就是对你那个亿级的表只做Insert处理,而另外一张表C是作为一张缓存表,照你讲的需求至多也就1000条数据吧。每次将这个表的数据插入到那个亿级的表后,就删除对应的数据。因为数据量少,所以感觉就算频繁做检索或者删除处理压力应该不会太大。批量插一千次,大概一共2千万条数据。
现在Update的速度非常快,几乎是秒更新,但是第一步Delete效率非常慢,可能是要维护索引,所以耗时。
这又什么建议吗?
SQL语句可以斟酌一下。
历史就是历史,历史是不用更新的,当前就是当前,在当前数据插入的时候就和历史区别开了。
增加一个CreateTime(DateTime)或BatchId(Int) ,用另外一张只有一个字段的表CreateTime(DateTime)或BatchId(Int)记录当前
2、没有索引只是让插入速度达到最快,但是查询速度就慢的可怜,经常会出现表扫描,对性能影响特别大,比如你插入前更新数据,如果没有索引就会特别慢。
3、采用业务表和历史表的方法,首先,对业务表和历史表分区,采取同样的方案,然后每次数据量达到一定规模,就把业务表中的分区切换到历史表,这样总保持业务表一定的数据,这个操作是针对数据库元数据操作,切换分区也只是改变页面的指针而已,不会引起大量的io操作。
4、改变数据库的存储结构,采用多文件多磁盘,提高io速度。
5、如果可以,把数据全部放入内存。
另外,也可以用下面较为复杂的方式:
建两个表,一个归档A,一个当前C,操作的时候
1把当前表中要归档的数据插入到归档表(索引加快查询),其实就是
insert into A select ....,'Archied' from C,I where 条件(I是接口表或者说是新进入的数据)
2然后把不需要归档的数据和新增的数据插入新建立的表1
也是insert + select操作
3删除当前表
4把新建立的表1重命名为当前表
5归档表和当前表重建分区视图供外部查询
以上所有步骤都不存在update和delete,效率会高很多.