小议SQLServer批量更新的优化

听全老大的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系统存储过程,通过使用它就能实现高效率的调用因为:

  1. 因为在 sp_executesql 中,Transact-SQL 语句的实际文本在两次执行之间未改变,所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。这样,SQL Server 不必编译第二条语句。
  2. 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 字符串相似,所以查询优化程序更有可能反复使用执行计划

*/

 

这时候我想起电影<霍元甲>中的一句话:我本以为,武术本无高低之分,只有练武的人才有高低之分.
呵呵 

时间: 2024-09-27 23:34:30

小议SQLServer批量更新的优化的相关文章

SQLServer批量更新两个关联表数据的方法_MsSql

本文实例讲述了SQLServer批量更新两个关联表数据的方法.分享给大家供大家参考,具体如下: 方式1: UPDATE a SET WtNo=b.NO from WT_Task a INNER JOIN WT_BasicInformation b ON a.WtId=b.ID; 方式2: UPDATE a SET a.WtNo=b.NO FROM WT_Task a,WT_BasicInformation b WHERE a.WtId=b.ID; 希望本文所述对大家SQL Server数据库程序

SQLServer批量更新两个关联表数据的方法

本文实例讲述了SQLServer批量更新两个关联表数据的方法.分享给大家供大家参考,具体如下: 方式1: UPDATE a SET WtNo=b.NO from WT_Task a INNER JOIN WT_BasicInformation b ON a.WtId=b.ID; 方式2: UPDATE a SET a.WtNo=b.NO FROM WT_Task a,WT_BasicInformation b WHERE a.WtId=b.ID; 希望本文所述对大家SQL Server数据库程序

性能优化——统计信息——SQLServer自动更新和自动创建统计信息选项

原文:性能优化--统计信息--SQLServer自动更新和自动创建统计信息选项 原文译自:http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913          

mysql批量提交的优化

.背景 用户修改布局时,需要批量更新mysql的xxxx_layout_xxxx表.批量操作的数据量是2-30条/次.批量操作是这次项目在技术上比较关键的一个点,之前批量操作做过性能上的测试,mysql端问题不大,7000+tps,Java端的效率有些差,有优化空间. 对批量的性能进行了测试,优化.过程如下. 经测试,批量更新30条记录的时间是35ms.由于数据在mysql服务端中会有内存缓存,批量更新30条的时间用了35ms,感觉有些长,试图找出原因. 使用截包工具(这里用的ethereal)

sql server-SQL Server怎么批量更新提高效率?

问题描述 SQL Server怎么批量更新提高效率? C# 将ID=1,ID=2,ID=3······ID=10这十条数据的NAME字段都改成ABC 用foreach操作很耗时间,有没有什么批量的方法提高效率? 解决方案 sql语句优化一下,where id between 或者 in(1,10) 解决方案二: SQL SERVER的批量插入与批量更新Sql Server根据记录集批量更新数据库如何update sql 批量更新(sql server) 解决方案三: 楼上说的对,使用betwee

sql2005 批量更新问题的解决方法_mssql2005

首先注意Statement 和PreparedStatement的问题 Statement sm = cn.createStatement(); sm.addBatch(sql1); sm.addBatch(sql2); ... sm.executeBatch() 用Statement的好处就是每次可以直接传一个SQL语句进去,不用管那么多.可是在数据量比较大的时候,应该会对效率有影响.不建议使用. PreparedStatement ps = cn.preparedStatement(sql)

sql server 批量更新问题的解决方法

Statement 和PreparedStatement的问题 Statement sm = cn.createStatement(); sm.addBatch(sql1); sm.addBatch(sql2); ... sm.executeBatch() 用Statement的好处就是每次可以直接传一个SQL语句进去,不用管那么多.可是在数据量比较大的时候,应该会对效率有影响.不建议使用. PreparedStatement ps教程 = cn.preparedStatement(sql);

MySQL实现批量插入以优化性能的教程_Mysql

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长.特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久.因此,优化数据库插入性能是很有意义的. 经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考. 1. 一条SQL语句插入多条数据.常用的插入语句如:   INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VA

Ibatis怎么做批量更新?????

问题描述 <insert id="updateStatusBatch" parameterClass="hashMap">   update   Question   set   status = #status#   <dynamic prepend="where questionId in">   <isNotNull property="actionIds">   <iterat