生产数据库更新忙，没有超时

目录

介绍

使用代码

介绍

我正在开发一个大数据仓库。我们拥有来自具有不同数据库环境和体系结构的多个事务系统的数据。COBOL平面文件，Oracle，SQL服务器是主要的数据源。每夜进程从所有数据源导入每日事务数据（增量更改），并组合形成单个客户视图数据库。

通过比较Surname，Firstname，DOB，Sex，前两个地址行的第一个字符，ETL过程可以从所有事务系统中找到Common客户——根据上述条件字段创建HASHBYTE校验和值，并在Checksum字段上使用GRUOP BYE。因此，ETL过程创建一个重复数据删除的Customer表。

然后使用最新的联系人详细信息（电话，移动电话，电子邮件，地址等）更新新的Staging Customer表。此外，Customer表经过某些数据标准化和验证。例如，将所有电话号码设为国际格式，验证电子邮件地址，删除不良数据等。

每个唯一的客户(Customer)都会获得一个客户(Customer)ID（CID）。在下一个ETL过程中，CID可以合并或解除合并作为事务系统上的更改的一部分。我在本文中仅描述了Customer表，但是ETL过程基于CID创建了大量的其他表（例如，PRODUCT）。

最后，我必须将Staging Customer表与生产实时客户(Live Customer)表同步，因为Web和Mobile API都是实时的，所以在白天也要同步，即使是午夜也要同步。由于Customer表具有数百万条记录和CID合并的可能性，因此从staging Customer表更新Live Customer表并不容易，而且成本不低。此过程需要很长时间，并导致其他应用程序运行超时。要更新实时表格，请按以下步骤操作：

• 锁定客户(Customer)表
• 在ETL过程中加载在实时表上创建的任何新客户
• 截断实时(live)客户表
• 从Staging Customer表加载数据
• 重新构建所有索引
• 解锁客户(Customer)表

现在问题是上面的步骤需要将近5-10分钟，而其他Web和移动应用程序会因超时而受到影响。我已使用临时表（DROP和RENAME）解决了该问题。系统工作了，只有一秒钟锁定客户表！

步骤：

• 在Production数据库上创建临时Customer表。
• 从暂存区域将数据加载到临时表。
• 在临时表上创建必需的索引（在我的例子中也是一个全文索引）
• 在ETL过程中将在活动表上创建的任何新客户加载到临时表。
• DROP 实时(live)Customer表
• RENAME临时表生存客户表

使用代码

代码在生产数据库上创建tmp_Customer并从临时(staging)表加载数据。下一步创建所有必要的索引。从实时客户表中收集任何缺少的客户详细信息——在ETL过程中，在Production 数据库中创建Customers。最后DROP 实时(live)customer表和RENAME临时表生活。代码仅显示Customer表如何在Production 中刷新。您可以重复所有其他表的代码。在继续之前备份必要的表。

-- Microsoft SQL Server 2014 (SP2-CU16) (KB4482967) - 12.0.5626.1 (X64)

-- Author: Prasannakumaran Sarasijanayanan

IF OBJECT_ID('dbo.tmp_Customer', 'U') IS NOT NULL DROP TABLE dbo.tmp_Customer;

　

CREATE TABLE [dbo].[tmp_Customer](

[CID] [bigint] NOT NULL,

[FirstName] [varchar](50) NULL,

[SurName] [varchar](50) NULL,

[Address1] [varchar](50) NULL,

[Address2] [varchar](50) NULL,

[Address3] [varchar](50) NULL,

[Address4] [varchar](50) NULL,

[Address5] [varchar](50) NULL,

[Title] [varchar](10) NULL,

[Sex] [varchar](1) NULL,

[DOB] [varchar](12) NULL,

[Fulladdress] [varchar](200) NULL,

[email] [varchar](100) NULL,

[Mobile] [varchar](20) NULL,

[Fullname] [varchar](100) NULL

-- Other columns are purposely removed

PRIMARY KEY CLUSTERED

(

[CID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

　

--Load data from ETL staging area

insert into tmp_customer

( CID, FirstName, SurName, Address1, Address2, Address3, Address4, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname)

select CID, FirstName, SurName, Address1, Address2, Address3, Address4, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname from CCF

--========================= Creating Indices ==================================

--Mobile

CREATE NONCLUSTERED INDEX [NonClusteredIndex-Mobile] ON [dbo].[tmp_Customer]

(

[Mobile] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

　

--CREATE FULLTEXT CATALOG Customer_FullText WITH ACCENT_SENSITIVITY = OFF


declare @sql varchar(max)

Declare @CONSTRAINT_NAME varchar (256)

SELECT @CONSTRAINT_NAME=KU.CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU

ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND

KU.table_name='tmp_customer'

ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

--BUILD Dynamic SQL

set @sql=

'CREATE FULLTEXT INDEX ON tmp_customer

(

Email

Language 1033,

Fullname

Language 1033,

Fulladdress

Language 1033

)

KEY INDEX ' + @CONSTRAINT_NAME + '

ON Customer_FullText

WITH STOPLIST = OFF

-- SEARCH PROPERTY LIST = OFF

'

EXEC (@sql)

--insert Customer data created during ETL

INSERT INTO tmp_Customer

( CID, FirstName, SurName, Address1, Address2, Address3, Address4, Address5, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname)

SELECT CID, FirstName, SurName, Address1, Address2, Address3, Address4, Address5, Title, Sex, DOB, Fulladdress, email, Mobile, Fullname

FROM Customer P WHERE NOT EXISTS (SELECT NULL FROM tmp_Customer F WHERE P.CID = F.CID)

--Finally Drop and Rename table

IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL DROP TABLE dbo.Customer;

EXEC sp_rename 'tmp_customer', 'Customer'

 

原文地址：https://www.codeproject.com/Tips/5062305/Busy-Production-Database-update-without-timeout