SQLServer批量更新、批量插入
来源:互联网 发布:sql server count 慢 编辑:程序博客网 时间:2024/05/16 07:31
最近遇到这样一个问题,项目中几个地方存储的地址只有一个字段,省市区县道路都在一起,地区检索用like,这简直是无法忍受,经商讨决定对原地址字段进行拆分,但是数据量相对较大,其中最少的一张表中有70万的数据,多的将近千万,下面开启拆分之路:
由于地址没有什么规律性,纯SQL语句不能满足需要,基本思路是使用C#配合SQL来实现。
方案一、最容易想到的,也是最开始想到的就是,把数据一行一行的取出来,经过运算、比对,找到省市县之后,再根据id把数据更新回去。具体方法不再实现,效率极低。频繁的数据库打开、关闭、读写,操作测试库都不能忍受,正式库上高并发,极容易超时出错。
方案二、上面是一条语句一条语句执行的,比它效率高点的是,一次性生成一定数量的SQL语句,放在一个list中,然后利用事务一次提交。这样做可以大幅度减少数据库建立连接、关闭连接的消耗,但是一次执行几百条或者几千条的SQL语句,磁盘读写量还是很大的,而且如果一条数据超时或者出错,则事务回滚,这个也是不能忍的。
就实验来说,上面两种方案耗费的时间都相差不多,70万条的数据差不多得一上午的时间去执行,那将近一千万的怎么活?查了很多资料,找到一很简洁的方法。
方案三:
1、在SQLserver上建立一个自定义表类型(在本例中简称表变量,此表变量非存储过程中声明的@tablename),其中 有三个字段,id,省,市
CREATE TYPE [dbo].[adress_update] AS TABLE( [id] [INT] NULL, [province] [VARCHAR](20) NULL, [city] [VARCHAR](40) NULL )
2、创建存储过程(写SQL也可以)
CREATE PROCEDURE [dbo].[proc_update_address] @addresstable adress_update READONLY AS BEGIN UPDATE bizinfo SET province=b.province,city=b.city FROM @addresstable b WHERE b.id=bizinfo.id END
这个存储过程只有 一个参数,类型就是刚才建的那个自定义表类型,语句很简单就是 一个批量更新,看到这里,很多人都差不多明白了,就是把一个表作为一个变量来传递 ,好了,看最后的程序实现
3、程序实现,用的控制台程序,个人感觉控制台程序简洁、方便
try{ DataTable dt = new DataTable(); dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("province", typeof(string)); dt.Columns.Add("city", typeof(string)); DataTable dtbizinfo = DBHelper.GetTable("select Id,Address,Unit from " + table + " where Address IS NOT NULL and id>" + start + " and id<=" + end); for (int i = 0; i < dtbizinfo.Rows.Count; i++) { string province = ""; string city = ""; . . . //把表dtbizinfo中的数据进行拆分组合出一个新的DataTable,此处省去具体的拆分步骤 DataRow dr = dt.NewRow(); dr["Id"] = dtbizinfo.Rows[i]["Id"].ToString(); dr["province"] = province; dr["city"] = city; dt.Rows.Add(dr); } SqlParameter[] param = { new SqlParameter("@addresstable", dt) }; DBHelper.RunProcedureNo("proc_update_address", param);//执行存储过程,不返回结果 } catch (Exception ex) { Console.WriteLine(ex.Message); }
思路很简单,就是把一张表作为一个变量传递过去,从而实现批量更新,
这里是把DataTable作为一个参数传递过去的,这个就吊炸天了,这样的话可以实现跨数据库、跨服务器去更新、删除、插入数据,MySQL,MSSQL,Orical等数据库之间随意操作,简直是不能太方便。
最后看看效率,做了这么多,写了这么,没有效率的话什么都不是,在实际项目中,用方案三更新一万条数据,花费时间不到一秒钟。加上地址拆分的运算,70万条数据的表更新完毕,耗时4分钟左右(主要是地址拆分耗费的,不然也就不到1分钟),完爆上面的两个方案,数据插入的操作类似,不再赘述。
除此之外,还遇到一个问题
DataTable dt = new DataTable();
dt.Columns.Add(“Id”, typeof(int));
dt.Columns.Add(“province”, typeof(string));
dt.Columns.Add(“city”, typeof(string));
这块代码如果按下面这样写,就会报错(报的错误大概内容是不能把字符串转换为数字)
DataTable dt = new DataTable();
dt.Columns.Add(“province”, typeof(string));
dt.Columns.Add(“city”, typeof(string));
dt.Columns.Add(“Id”, typeof(int));
就是更改了一下table列的添加顺序,仔细查看错误提示,似乎是把province的数据存储或者更新到了id的字段上,求知道原因的大神解惑。
补充上面问题的答案:
C#代码中的DataTable dt的字段跟表变量adress_update中的字段没有对应关系的,只需要类型一样,顺序一样,即DataTable的第一列对应表变量的第一列,以此类推。所以,上面的代码
DataTable dt = new DataTable();
dt.Columns.Add(“Id”, typeof(int));
dt.Columns.Add(“province”, typeof(string));
dt.Columns.Add(“city”, typeof(string));
也可以改为:
DataTable dt = new DataTable();
dt.Columns.Add(“a”, typeof(int));
dt.Columns.Add(“b”, typeof(string));
dt.Columns.Add(“c”, typeof(string));
- SQLServer批量更新、批量插入
- Mybatis 批量插入、批量更新
- mybatis3批量更新 批量插入
- 批量插入,批量更新动作
- jdbc批量插入、批量删除、批量更新
- JDBC批量插入、批量删除、批量更新
- JDBC批量插入、批量删除、批量更新
- jdbc批量插入、批量删除、批量更新
- jdbc批量插入、批量删除、批量更新
- jdbc批量插入、批量删除、批量更新
- dbc批量插入、批量删除、批量更新
- JDBC批量插入、批量删除、批量更新
- 批量更新插入
- 批量插入与更新
- Mybatis批量插入、更新
- sqlserver如何批量插入数据
- ibatis 的批量插入 批量更新数据
- mysql 批量插入和批量更新的
- WAL机制
- 如何将导航栏始终固定在窗口顶部:
- C++多线程系列(一)CreateThread和_beginthreadex区别
- 写博客的一点小心得
- service
- SQLServer批量更新、批量插入
- okhttp网络请求框架
- POJ 3487 The Stable Marriage Problem
- 新型电商模式
- linux网络编程(二)——TCP编程
- Servlet学习笔记(Tomcat目录结构)
- Topcoder SRM 661 Div1 Easy: MissingLCM
- MySQL新建用户,授权,删除用户,修改密码
- java对象、重载、访问控制符