小议SQLServer批量更新的优化

php中文网

发布时间：2016-06-07 15:51:48

1389人浏览过

来源于php中文网

原创

听全老大的 JDBC 课的时候，听到一节是讲在利用 JDBC 中处理批量更新oracle数据时候的特性，让我很为 JDBC 的特性感的兴奋，利用这个特性可以在批量更新数据的时候不同往常一样每次都需要传送完成的 SQL 语句到数据库中。其中示范代码如下 : 1 import java.s

听全老大的jdbc课的时候，听到一节是讲在利用jdbc中处理批量更新oracle数据时候的特性，让我很为jdbc的特性感的兴奋，利用这个特性可以在批量更新数据的时候不同往常一样每次都需要传送完成的sql语句到数据库中。其中示范代码如下:

1 import java.sql.*;
2
3 publicclass BatchUpdates
4 {
5 publicstaticvoid 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

如图表示

小议SQLServer批量更新的优化

客户段发送一条 select 之类的T-SQL语句，首先会进过TDS使用ODS(output data strem)来包装数据之后再发送到服务器端，在服务器端会有一个Net-librales的程序对各种网络协议进行监听，不管此时你使用的是tcp/ip还是什么其他的协议，Net-librales会根据连接近来的不同协议进行分类，然后归类集中监听、处理数据.。

当数据到达服务器端之后交由SQL引擎来处理，如下图所表示，

iWebShop开源商城系统

iWebShop是一款基于PHP语言及MYSQL数据库开发的B2B2C多用户开源免费的商城系统，系统支持自营和多商家入驻、集成微信商城、手机商城、移动端APP商城、三级分销、视频电商直播、微信小程序等于一体，它可以承载大数据量且性能优良，还可以跨平台，界面美观功能丰富是电商建站首选源码。iWebShop开源商城系统 v5.14 更新日志：新增商品编辑页面规格图片上传优化商品详情页面规格图片与主图切

下载

小议SQLServer批量更新的优化

在 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。

如果能和存贮过程结合起来一起使用在性能上能进一步提供
比如
CREATETABLE May1998Sales
    (OrderID      INT      PRIMARYKEY,
    CustomerID      INT      NOTNULL,
    OrderDate      DATETIME   NULL
        CHECK (DATEPART(yy, OrderDate) =1998),
    OrderMonth      INT
        CHECK (OrderMonth =5),
    DeliveryDate   DATETIME   NULL,
        CHECK (DATEPART(mm, OrderDate) = OrderMonth)
    )

CREATEPROCEDURE InsertSales @PrmOrderIDINT, @PrmCustomerIDINT,
                 @PrmOrderDateDATETIME, @PrmDeliveryDateDATETIME
AS
DECLARE@InsertStringNVARCHAR(500)
DECLARE@OrderMonthINT

-- Build the INSERT statement.
SET@InsertString='INSERT INTO '+
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) ASCHAR(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 字符串相似，所以查询优化程序更有可能反复使用执行计划

*/