小议SQLServer批量更新的优化
来源:互联网 发布:linux snmp yum 编辑:程序博客网 时间:2024/05/17 04:11
听全老大的JDBC课的时候,听到一节是讲在利用JDBC中处理批量更新oracle数据时候的特性,让我很为JDBC的特性感的兴奋,利用这个特性可以在批量更新数据的时候不同往常一样每次都需要传送完成的SQL语句到数据库中。其中示范代码如下:
1 import java.sql.*;
2
3 public class BatchUpdates
4 {
5 public static void main(String[] args)
6 {
7 Connection conn = null;
8 Statement stmt = null;
9 PreparedStatement pstmt = null;
10 ResultSet rset = null;
11 int i = 0;
12
13 try
14 {
15 DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
16
17 String url = "jdbc:oracle:oci8:@";
18 try {
19 //检查是否配置JDBC环境变量
20 String url1 = System.getProperty("JDBC_URL");
21 if (url1 != null)
22 url = url1;
23 } catch (Exception e) {
24 //如果是在集成开发环境导入了JDBC的话可以注释这句
25 }
26
27 // 连接到数据库用 scott
28 conn = DriverManager.getConnection (url, "scott", "tiger");
29
30 stmt = conn.createStatement();
31 try { stmt.execute(
32 "create table mytest_table (col1 number, col2 varchar2(20))");
33 } catch (Exception e1) {}
34
35 //
36 // 批量插入新值.
37 //
38 pstmt = conn.prepareStatement("insert into mytest_table values (?, ?)");
39
40 pstmt.setInt(1, 1);
41 pstmt.setString(2, "row 1");
42 pstmt.addBatch();
43
44 pstmt.setInt(1, 2);
45 pstmt.setString(2, "row 2");
46 pstmt.addBatch();
47
48 pstmt.executeBatch();
49
50 //
51 // 查询 输出结构集
52 //
53 rset = stmt.executeQuery("select * from mytest_table");
54 while (rset.next())
55 {
56 System.out.println(rset.getInt(1) + ", " + rset.getString(2));
57 }
58 }
59 catch (Exception e)
60 {
61 e.printStackTrace();
62 }
63 finally
64 {
65 if (stmt != null)
66 {
67 try { stmt.execute("drop table mytest_table"); } catch (Exception e) {}
68 try { stmt.close(); } catch (Exception e) {}
69 }
70 if (pstmt != null)
71 {
72 try { pstmt.close(); } catch (Exception e) {}
73 }
74 if (conn != null)
75 {
76 try { conn.close(); } catch (Exception e) {}
77 }
78 }
79 }
80 }
在MSSQLServer中呢,没有这个实用的特性吗,随后的几天自己开始注意了以下sql Server的架构,sql server号称是以C/S模式架构,其实它的前身Sybase DataServer 才是C/S模式关系型的第一款数据库。既然是C/S模式肯定就包含一个客户端与数据库段的交互过程,SQLServer在客户端使用一种称为TDS的协议来与服务器的Sqlserver服务器来进行数据库的交互,
TDS (Table Data Strem)
客户端使用称为表格格式数据流 (TDS) 的 SQL Server 专用应用程序级协议来发送 SQL 语句。SQL Server 2000 接受 TDS 的下列版本
SQL Server 2000 的 SQL Server 客户端组件版的客户端发送的 TDS 8.0
SQL Server 7.0 版的 SQL Server 客户端组件版的客户端发送的 TDS 7.0
SQL Server 6.5、6.0 和 4.21a 中运行 SQL Server 客户端组件的客户端所发送的 TDS 4.2
如图表示
客户段发送一条 select 之类的T-SQL语句 ,首先会进过TDS使用ODS(output data strem)来包装数据之后再发送到服务器端,在服务器端会有一个Net-librales的程序对各种网络协议进行监听,不管此时你使用的是tcp/ip还是什么其他的协议,Net-librales会根据连接近来的不同协议进行分类,然后归类集中监听、处理数据.。
当数据到达服务器端之后交由SQL引擎来处理,如下图所表示,
在 SQL Server 7.0 中,绝大多数来自客户机的功能调用都是通过 RPC(远程存贮过程控制) 消息进行的(但这不是本文想说明的重点),通常,作为 TDS SQL 语言消息的 SQL 语句直接在编译一端执行,再经过查询优化器进行一定的优化处理再将结果通过表达试服务返回给客户机,
在查询优化器中每编译(优化)一条T-SQL语句就会生成其对应的执行计划就是我们常说的缓存。但是平常在客户端提交上来的T-SQL 经过TDS的包装,即使2条T-SQL语句完全也不会生成完全相同的TDS格式的数据流,所以查询优化器编译之后会认为是2条不同的执行计划,所以每次都要去重新编译再缓存,浪费了不必要的时间。难道sqlserver真的做不到jdbc那样批量提交的优化功能吗?
其实在Sqlserver中有一个sp_executesql系统存储过程,通过使用它就能实现高效率的调用因为:
- 因为在 sp_executesql 中,Transact-SQL 语句的实际文本在两次执行之间未改变,所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。这样,SQL Server 不必编译第二条语句。
- Transact-SQL 字符串只生成一次。
整型参数按其本身格式指定。不需要转换为 Unicode。
比如
CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
GO
/*
在该过程中使用 sp_executesql 比使用 EXECUTE 执行字符串更有效。使用 sp_executesql 时,只生成 12 个版本的 INSERT 字符串,每个月的表 1 个。使用 EXECUTE 时,因为参数值不同,每个 INSERT 字符串均是唯一的。尽管两种方法生成的批处理数相同,但因为 sp_executesql 生成的 INSERT 字符串相似,所以查询优化程序更有可能反复使用执行计划
*/
呵呵
- 小议SQLServer批量更新的优化
- sqlserver批量更新关联表的数据
- SQLServer批量更新、批量插入
- ElasticSearch批量更新索引的优化
- EF批量更新优化
- mysql批量插入,更新优化
- mysql 批量更新性能优化
- mysql批量更新语句优化
- Oracle的update语句优化研究 批量更新
- 关于Neo4j和Cypher批量更新和批量插入优化的5个建议
- 关于Neo4j和Cypher批量更新和批量插入优化的5个建议
- sqlserver 批量更新表中的值 把某个字段更新成另外一个表的某个字段
- 两个超大表做关联更新的优化 之 批量更新
- DataAdapter 的批量更新
- DataAdapter 的批量更新
- GridView的批量更新
- gridview的批量更新
- hibernate 的批量更新
- 用VC制作带弹出式菜单的按钮
- 盗QQ的密码软件模拟实现技术
- 如何修复:Windows上面的WScript的脚本(.vbs)不能执行了?
- Ms05039蠕虫的VC实现
- HOOK
- 小议SQLServer批量更新的优化
- 20种看asp源码的方法
- 网页元素纵向滚动
- 使用md5校验和算法保护文件
- 关于volatile——编写多线程程序的好帮手
- 用VC++6.0 Sockets API实现聊天室程序
- 建立tcp/ip连接,vc6实现客户端(发送数据)
- socket实现tcp/ip服务端源码,无线程
- 很巧妙的进程防杀方法