小议SQLServer批量更新的优化-阿里云开发者社区

开发者社区> 宝珠> 正文

小议SQLServer批量更新的优化

简介: 听全老大的JDBC课的时候,听到一节是讲在利用JDBC中处理批量更新oracle数据时候的特性,让我很为JDBC的特性感的兴奋,利用这个特性可以在批量更新数据的时候不同往常一样每次都需要传送完成的SQL语句到数据库中。
+关注继续查看

听全老大的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.56.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的包装,即使2T-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 字符串相似,所以查询优化程序更有可能反复使用执行计划

*/

 

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 批量主分区备份(Multiple Jobs)
原文:SQL Server 批量主分区备份(Multiple Jobs) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 案例分析(Case) 方案一(Solution One) 方案二(Solution Two) 方案三(So...
963 0
PolarDB PostgreSQL更新路计划
PolarDB PostgreSQL将持续发布对用户有价值的功能。当前我们计划了5个阶段:
86 0
Sql性能检测工具:Sql server profiler和优化工具:Database Engine Tuning Advisor
原文:Sql性能检测工具:Sql server profiler和优化工具:Database Engine Tuning Advisor 一、工具概要     数据库应用系统性能低下,需要对其进行优化,     如果不知道问题出在哪里,可以使用性能检测工具sql server profiler。
1660 0
SqlServer性能检测和优化工具使用详细
工具概要         如果你的数据库应用系统中,存在有大量表,视图,索引,触发器,函数,存储过程,sql语句等等,又性能低下,而苦逼的你又要对其优化,那么你该怎么办?哥教你,首先你要知道问题出在哪里?如果想知道问题出在哪里,并且找到他,咱们可以借助本文中要讲述的性能检测工具--sql server profiler(处在sql安装文件--性能工具--sql server profiler)     如果知道啦问题出现在哪里,如果你又是绝世高手,当然可以直中要害,写段代码给处理解决掉,但是如果你不行,你做不到,那么也无所谓,可以借助哥的力量给你解决问题。
924 0
SQL Server Profiler和数据库引擎优化顾问
原文:SQL Server Profiler和数据库引擎优化顾问  简介           说到Sql的【性能工具】真是强大,SQL Server Profiler的中文意思是SQL Server事件探查,这个到底是做什么用的呢?我们都知道探查的意思大多是和监视有关,其实这个SQL Server Profiler就是一个Sql的监视工具,可以具体到每一行Sql语句,每一次操作,和每一次的连接。
1087 0
hmailserver批量添加用户
hmailserver批量添加用户 1. 把以下代码保存到 文本文件 batadduser.vbs Option Explicit On Error Resume Next wscript.
1916 0
SQL更新部分字段或者插入新数据
SQL更新部分字段或者插入新数据 因为业务的原因,一张表的数据需要整合两张外键关联不强的表数据,这里涉及到更新部分字段或者插入新数据。今天简单的做个笔记,后续有需要再补充完整 测试表结构: CREATE TABLE `test` ( `id` int(10) NOT NULL, `name` v.
1407 0
SQL Server修改表结构后批量更新所有视图
最近修改了数据库表结构,数据同步的时候出了问题,发现很多数据明明已经修改,但是通过视图筛选出来的还是原来的数据,所以怀疑应该是视图缓存了数据,在园子里找到下面的博文,在这里做个记录备忘。   原文链接:http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html   我们在使用SqlServer时经常遇到这种情况,当修改某个表的结构后,相关的视图就不对了而导致程序错误,因此就有个下面这个存储过程。
873 0
SQL Server优化器特性-隐式谓词
原文:SQL Server优化器特性-隐式谓词 我们都知道,一条SQL语句提交给优化器会产生相应的执行计划然后执行输出结果,但他的执行计划是如何产生的呢?这可能是关系型数据库最复杂的部分了.这里我为大家介绍一个有关SQL Server优化器的特性-隐式谓词,并简单介绍在此特性下如何根据场景控制优化器的行为.
771 0
+关注
宝珠
说实话不如做实事
243
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载