大数据量跨系统人员信息同步比较高效方案

1 篇文章 0 订阅
1 篇文章 0 订阅
 

《大数据量跨系统人员信息同步比较高效方案》By landray LD 2011-09-01

背景:

1、  用户有近3w的用户信息在其他系统中需要同步到ad目录中去

2、  原系统没有提供修改时间,只能提供即时的全人员信息表

思路:

1、  我们需要每次都比较3w用户的变更情况,得出1张变化差异表

2、  根据差异表修改ad目录

方案(只介绍比较用户得到差异表):

1、  建立中间表、临时表、历史表和差异表

2、  取信息表到中间临时表,另外再增加2列,1列是整合人员的唯一编号,2列是加总所有的需要变更的信息的列

3、  临时表与历史表分别进行新增、删除、变更的比较,产生的差异数据写入到差异表中

4、  同步成功后把临时表数据完全写入历史表,备下次进行比较

测试效果:

Ibm X61上win7 64位系统装sql server 2005,运行1.6w人员信息运行完成存储过程只需要几秒!

 

附录1:创建几个表的sql

drop table [dbo].[union_active_directory]

go

 

CREATE TABLE [dbo].[union_active_directory] (

  [source] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_nm] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [area_id] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,

  [area_nm] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [company_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_nm] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [postname] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [brand] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [mobile] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [telephone] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [email] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [memo] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [lastmodify] datetime NULL,

  [mark] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,

  CONSTRAINT [PK_union_active_directory] PRIMARY KEY CLUSTERED ([source], [user_id], [company_id])

)

ON [PRIMARY]

go

 

drop table [dbo].[union_active_difference]

go

 

CREATE TABLE [dbo].[union_active_difference] (

  [source] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [unique_id] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_nm] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [area_id] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,

  [area_nm] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [company_unid] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_nm] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [postname] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [brand] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [mobile] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [telephone] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [email] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [memo] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

 

  [o_user_nm] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [o_area_id] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,

  [o_area_nm] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [o_company_nm] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [o_postname] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [o_brand] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [o_mobile] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [o_telephone] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [o_email] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [o_memo] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

 

  [action_type] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [action_created] datetime NOT NULL,

  [action_modify] datetime NULL,

  [action_mark] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

 

)

ON [PRIMARY]

go

 

drop table [dbo].[union_active_temporary]

go

 

CREATE TABLE [dbo].[union_active_temporary] (

  [source] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_nm] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [area_id] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,

  [area_nm] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [company_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_nm] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [postname] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [brand] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [mobile] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [telephone] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [email] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [memo] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [lastmodify] datetime NULL,

  [mark] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,

  [unique_id] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_unid] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [memo_all] varchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,

  CONSTRAINT [PK_union_active_temporary] PRIMARY KEY CLUSTERED ([unique_id])

)

ON [PRIMARY]

go

 

drop table [dbo].[union_active_history]

go

 

CREATE TABLE [dbo].[union_active_history] (

  [source] varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [user_nm] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [area_id] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,

  [area_nm] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [company_id] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_nm] varchar(100) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [postname] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [brand] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [mobile] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [telephone] varchar(40) COLLATE Chinese_PRC_CI_AS NULL,

  [email] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,

  [memo] varchar(250) COLLATE Chinese_PRC_CI_AS NULL,

  [lastmodify] datetime NULL,

  [mark] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,

  [unique_id] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [company_unid] varchar(40) COLLATE Chinese_PRC_CI_AS NOT NULL,

  [memo_all] varchar(500) COLLATE Chinese_PRC_CI_AS NOT NULL,

  CONSTRAINT [PK_union_active_history] PRIMARY KEY CLUSTERED ([unique_id])

)

ON [PRIMARY]

 

Go

 

附录2:创建存储过程的sql

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:          <LD>

-- Create date: <2011/09/01>

-- Description: <比较2表的差异>

-- =============================================

drop PROCEDURE Procedure_test

go

CREATE PROCEDURE Procedure_test  

AS

BEGIN

         SET NOCOUNT ON;

 

--定义参数 操作类别,操作时间,操作标记

         DECLARE @action_type varchar(10)

         DECLARE @action_created varchar(20)

         DECLARE @action_mark varchar(10)

--       set @action_type = 'delete'

--       set @action_created = '2011-09-01 00:00:00'

--取服务器当前时间

         Select @action_created = CONVERT(varchar(100), GETDATE(), 20)

--操作标记设置初始化为未操作

         set @action_mark = 'n'

 

--临时表记录数

    DECLARE @countTemporary int

--历史表记录数

    DECLARE @countHistory int

        

--把数据先导入到处理库      

         delete from union_active_temporary

        insert into union_active_temporary(source, user_id, user_nm, area_id, area_nm, company_id, company_nm, postname, brand, mobile, telephone, email, unique_id, company_unid, memo_all)

                   select source, user_id, user_nm, area_id, area_nm, company_id,

                            company_nm, postname, brand, mobile, telephone, email,

                            (source + company_id + union_active_directory.user_id) as unique_id,

                            (source + company_id) as company_unid,

                            ( isnull( user_nm,'-' ) + isnull( area_nm,'-' ) + isnull( company_nm,'-' ) +

                            isnull( postname,'-' ) + isnull( brand,'-' ) + isnull( mobile,'-' ) +

                            isnull( telephone,'-' ) + isnull( email,'-' )) as memo_all

                   from union_active_directory

 

--比较如果数量少于100个,则退出

         select @countTemporary = count(*) from union_active_temporary

         select @countHistory = count(*) from union_active_history

         if @countHistory > (@countTemporary + 100)

          begin

                   return 0

          end

 

--开始事务

         begin transaction transaction_name

 

--插入不存在的行

         set @action_type = 'instert'

 

         insert into union_active_difference(source, unique_id, user_nm, area_id, area_nm, company_unid, company_nm, postname, brand, mobile, telephone, email, action_type, action_created, action_mark)

         select source, unique_id, user_nm, area_id, area_nm, company_unid, company_nm, postname, brand, mobile, telephone, email , @action_type , @action_created,@action_mark

         from union_active_temporary tt

         where unique_id not in (select unique_id from union_active_history )

 

--插入删除的行

         set @action_type = 'delete'

 

         insert into union_active_difference(source, unique_id, user_nm, area_id, area_nm, company_unid, company_nm, postname, brand, mobile, telephone, email, action_type, action_created, action_mark)

         select source, unique_id, user_nm, area_id, area_nm, company_unid, company_nm, postname, brand, mobile, telephone, email , @action_type , @action_created,@action_mark

         from union_active_history tt

         where unique_id not in (select unique_id from union_active_temporary )

 

--插入修改的行

         set @action_type = 'update'

 

         insert into union_active_difference(source, unique_id, user_nm, area_id, area_nm, company_unid, company_nm, postname, brand, mobile, telephone, email, action_type, action_created, action_mark

                   ,o_user_nm,o_area_id,o_area_nm,o_company_nm,o_postname,o_brand,o_mobile,o_telephone,o_email)

         select tt.source,tt.unique_id, tt.user_nm, tt.area_id, tt.area_nm, tt.company_unid, tt.company_nm, tt.postname, tt.brand, tt.mobile, tt.telephone, tt.email , @action_type , @action_created,@action_mark

                   ,ht.user_nm,ht.area_id,ht.area_nm,ht.company_nm,ht.postname,ht.brand,ht.mobile,ht.telephone,ht.email

         from union_active_history ht, union_active_temporary tt

         where ht.unique_id = tt.unique_id and ( not (ht.memo_all = tt.memo_all))

        

 

--处理完成后,把数据导到历史库备下次处理

         delete from union_active_history

         insert into union_active_history select * from union_active_temporary

        

--保存事务节点name2

--       save transaction name2

--事务跳转到name1

         if @@error<>0

         begin

                   rollback transaction transaction_name

                   return 0

         end

--事务结束

         else

         begin

                   commit transaction transaction_name

                   return 1

         end

 

END

 

 

  • 0
    点赞
  • 1
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

9309143

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值