一个进销存系统性能优化总结

男娘i 2022-08-07 05:35 215阅读 0赞

**系统的数据量分析及存在的关键性能瓶颈**

系统设计应支持每年2亿部上以终端的销售量业务处理，一般的终端设备从生产到消费整个生命周期最长为1年，超过一年的数据只需备份保存即可，不需要在系统中处理，即必须完整的保存1年发货、调度、零售等数据。初略的计算，最细粒度的表将超过2亿条记录。大多业务表(发货、调度、零售)的数据量也在1000万以上。由于系统中数据都有着明显的维度层次关系，如地区层次为国家、地区部、办事处，运营商层次为省公司、市分公司，产品层次为频段、制式、型号等。所有业务表中都有这些层次关系的数据字段。而处理多个维度时，每个维度下又分为多层次的树型结构数据，由于涉及到递归，性能一般很差，所以系统中普遍存在着对树型层次结构数据的处理优化问题。通过以上分析不难发现，系统关键性能瓶颈在于大数据表的操作和对树型层次结构数据的处理。

**1．3优化方向**

** **

经过对系统的了解和简单分析后，系统中普遍存在大数据量的表和层次结构的字段。对于大数据量表进行优化时除了必要的索引和从业务上划分为小的子表外，一个最重要的优化技术就是表分区了，依据业务特点进行适当分区后，它可有效的分离业务数据，达到高性能的更新和查找操作。系统中还有很多树型的数据需要处理，由于树型结构的存储一般非常简单，核心的问题在于对这个层次结构数据高效的查找上，如查询某个节点的所有子代节点。往往涉及到递归等查找操作，性能较差，它也是系统的瓶颈之一，必须提供一种高效查询的方案。接下来本文将详细的分析这两方面优化技巧。

** **

**2． 表分区优化**

** **

**2．1 表分区概述**

对大表的分割可从两个方面来入手,第一依据不同的业务场景特点来物理分割，因为不同的业务处理所关心的数据范围不同，按这个规则可将表分成多个子表，由于这部分处理较为简单，在此不作详细分析。第二就是数据表分区，对单个子表，如果数据仍然很大（超过百万）可进一步做分区处理。即按查询最频繁、性能提升明显的字段作为分区依据。这样不但可充分发挥CPU、硬盘并行处理能力而且可减少数据查询范围，即配合前台合适的查询条件可以不用扫描整个表，而只需要从一个或数个分区中并行读取数据，以提高查询效率。

**2．2表分区具体实施**

下面以调度业务（分销商之间调入调出业务）表为例来进行分区忧化，而对其分区的规则是创建时间，这是因为各字段中与业务关系最紧密的就是时间，往往用户最关心的是近三个月的数据，所以数据以月份为区间进行划分。为了让分区更智能，日后能自动维护分区，即交替、重复地使用固定的几个文件组（编号01~12），使数据能均匀的分布在各文件组上。

**2．2．1创建模拟数据组及文件组**

createdatabase GPMS;

useGPMS;

ALTERDATABASE GPMS ADD FILEGROUP FG1;

ALTERDATABASE GPMS ADD FILEGROUP FG2;

ALTERDATABASE GPMS ADD FILEGROUP FG3;

ALTERDATABASE GPMS ADD FILEGROUP FG4;

ALTERDATABASE GPMS ADD FILEGROUP FG5;

ALTERDATABASE GPMS ADD FILEGROUP FG6;

ALTERDATABASE GPMS ADD FILEGROUP FG7;

ALTERDATABASE GPMS ADD FILEGROUP FG8;

ALTERDATABASE GPMS ADD FILEGROUP FG9;

ALTERDATABASE GPMS ADD FILEGROUP FG10;

ALTERDATABASE GPMS ADD FILEGROUP FG11;

ALTERDATABASE GPMS ADD FILEGROUP FG12;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG1', FILENAME = 'c:\\DataBase\\GPMSF1.NDF') TOFILEGROUP FG1;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG2', FILENAME = 'd:\\DataBase\\GPMSF2.NDF') TOFILEGROUP FG2;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG3', FILENAME = 'e:\\DataBase\\GPMSF3.NDF') TOFILEGROUP FG3;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG4', FILENAME = 'F:\\DataBase\\GPMSF4.NDF') TOFILEGROUP FG4;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG5', FILENAME = 'c:\\DataBase\\GPMSF5.NDF') TOFILEGROUP FG5;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG6', FILENAME = 'd:\\DataBase\\GPMSF6.NDF') TOFILEGROUP FG6;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG7', FILENAME = 'e:\\DataBase\\GPMSF7.NDF') TOFILEGROUP FG7;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG8', FILENAME = 'F:\\DataBase\\GPMSF8.NDF') TOFILEGROUP FG8;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG9', FILENAME = 'c:\\DataBase\\GPMSF9.NDF') TOFILEGROUP FG9;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG10', FILENAME = 'd:\\DataBase\\GPMSF10.NDF') TOFILEGROUP FG10;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG11', FILENAME = 'e:\\DataBase\\GPMSF11.NDF') TOFILEGROUP FG11;

ALTERDATABASE GPMS ADD FILE (NAME = 'FG12', FILENAME = 'F:\\DataBase\\GPMSF12.NDF') TOFILEGROUP FG12;

上述代码创建了一个数据库GPMS(Global  Promotion Manager System)。使用表分区前必须为数据库分配文件组，这样系统才能把分区中的数据分离到不同盘符下的文件中，默认情况下所有数据都会存放在PRIMARY文件组中。因为系统中大多数据的有效期为一年，所以为数据库创建了除主文件组以外的十二个文件组，这十二个文件组用于存放每一个月份的数据，为了能同时并行的读取文件组，把这些文件组均匀的分布在各个盘符中（当然这还要依赖于具体服务器硬件配置）。完成后打开数据库属性的文件组如下图所示：

** **

**2．2．1创建分区函数和分区方案**

完成文件组的创建后，就可以为系统创建分区方案和分区函数了，一般一个分区函数服务于一个分区方案。由于前面分析过了，系统中最重要的分区规则为时间，很多业务数据查询最频繁的条件也是时间，所以在此处以时间为例来创建分区函数和分区方案，这样在后面的业务表创建时可共享该分区方案。

IF  EXISTS (SELECT \* FROM sys.partition\_schemesWHERE name = N'PSch\_Month')

DROPPARTITION SCHEME \[PSch\_Month\]

IF  EXISTS (SELECT \* FROM sys.partition\_functionsWHERE name = N'PFun\_Month')

DROPPARTITION FUNCTION \[PFun\_Month\]

CREATEPARTITION FUNCTION PFun\_Month(datetime)

RANGERIGHT FOR VALUES ('2011-2-1','2011-3-1','2011-4-1','2011-5-1','2011-6-1',

'2011-7-1','2011-8-1','2011-9-1','2011-10-1','2011-11-1','2011-12-1');

CREATEPARTITION SCHEME PSch\_Month

PARTITIONPFun\_Month TO (FG1, FG2,FG3,FG4,FG5,FG6,FG7, FG8,FG9,FG10,FG11,FG12);

** **

**2．2．2使用分区方案创建业务表**

IF  EXISTS (SELECT \* FROM sys.objects WHEREobject\_id = OBJECT\_ID(N'\[dbo\].\[Distribute\]') AND type in (N'U'))

DROPTABLE \[dbo\].\[Distribute\]

CREATETABLE \[dbo\].\[Distribute\](

\[ID\] \[int\] IDENTITY(1,1) NOT NULL,

\[OutAreaID\] \[int\] NOT NULL,

\[OutCustomer\] \[int\] NOT NULL,

\[ProductID\] \[int\] NOT NULL,

\[InAreaID\] \[int\] NOT NULL,

\[InCustomer\] \[int\] NOT NULL,

\[DistributeNum\] \[int\] NOT NULL,

\[Status\] \[smallint\] NOT NULL,

\[SendDate\] \[datetime\] NOT NULL,

CONSTRAINT \[PK\_Distribute\] PRIMARY KEYCLUSTERED

(

\[ID\], \[SendDate\] ASC

)WITH(PAD\_INDEX  = OFF,STATISTICS\_NORECOMPUTE  = OFF,IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS  =ON, ALLOW\_PAGE\_LOCKS  = ON)

) ONPSch\_Month(\[SendDate\]);

IF  EXISTS (SELECT \* FROM sys.objects WHEREobject\_id = OBJECT\_ID(N'\[dbo\].\[Distribute\_NoPartition\]') AND type in (N'U'))

DROPTABLE \[dbo\].\[Distribute\_NoPartition\]

CREATETABLE \[dbo\].\[Distribute\_NoPartition\](

\[ID\] \[int\] IDENTITY(1,1) NOT NULL,

\[OutAreaID\] \[int\] NOT NULL,

\[OutCustomer\] \[int\] NOT NULL,

\[ProductID\] \[int\] NOT NULL,

\[InAreaID\] \[int\] NOT NULL,

\[InCustomer\] \[int\] NOT NULL,

\[DistributeNum\] \[int\] NOT NULL,

\[Status\] \[smallint\] NOT NULL,

\[SendDate\] \[datetime\] NOT NULL,

CONSTRAINT \[PK\_Distribute\_NoPartition\]PRIMARY KEY CLUSTERED

(

\[ID\] ASC

)WITH(PAD\_INDEX  = OFF,STATISTICS\_NORECOMPUTE  = OFF,IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS  =ON, ALLOW\_PAGE\_LOCKS  = ON) ON \[PRIMARY\]

) ON\[PRIMARY\]

这里分别创建了两个结构相同，但一个使用了分区方案，一个没有使用分区方案，以便比对出它们的性能差异。

** **

**2．2．3生成模拟数据**

按照业务的需求，调度业务表将至少有1000万的数据量，下面我们将模拟的插入这些业务数据，分别插入到分区表和未分区表中，代码下如：

\--为表生成模拟数据,为保证数据不失一般性先插入十万条记录

truncatetable Distribute;

truncatetable Distribute\_NoPartition;

setnocount on;

declare@cnt int;

set@cnt = 100000;

while(@cnt> 0)

begin

INSERT INTO \[dbo\].\[Distribute\]

(\[OutAreaID\]

,\[OutCustomer\]

,\[ProductID\]

,\[InAreaID\]

,\[InCustomer\]

,\[DistributeNum\]

,\[Status\]

,\[SendDate\])

VALUES

(@cnt%100,

@cnt%99,

@cnt%98,

@cnt%100,

@cnt%101,

@cnt%102,

@cnt%200,

DATEADD(dd,@cnt%365,'2011-1-1'));

set @cnt = @cnt -1;

end;

\--为高效快速生成测试数据采用每次在原来记录数上增加一倍的方法生成,以下将生成万条记录

declare@cnt int;

set@cnt = 7;

while(@cnt> 0)

begin

INSERT INTO \[dbo\].\[Distribute\]

SELECT \[OutAreaID\]

,\[OutCustomer\]

,\[ProductID\]

,\[InAreaID\]

,\[InCustomer\]

,\[DistributeNum\]

,\[Status\]

,\[SendDate\]

FROMDistribute;

set @cnt = @cnt -1;

end;

\--把分区表中的数据全部复制到未分区表中

INSERTINTO \[dbo\].\[Distribute\_NoPartition\]

SELECT\[OutAreaID\]

,\[OutCustomer\]

,\[ProductID\]

,\[InAreaID\]

,\[InCustomer\]

,\[DistributeNum\]

,\[Status\]

,\[SendDate\]

FROM Distribute;

selectcount(ID) from Distribute\_NoPartition;

selectcount(ID) from Distribute;

** **

**2．2．4性能对照**

完成上述操作后，我们就可以进行比较分区和未分区方案的优劣了，以下为查询分析器中跟踪的真实数据，由于运行机器的不同运行效率也会有差异，但可以肯定的是在生产环境中分区方案运行效率将更优越，因为在硬件环境的配制上一般优于个人电脑，且都能同行并行计算和并行读取多个分区数据。以下为本机笔记本中效率对比：

首先开启脚本运行中性能跟踪开关：

setstatistics time on

setstatistics io on

分别使用分区和非分区来查询某个月份的记录，结果如下：

select\* from \[Distribute\] where SendDate > '2011-6-1' and SendDate <'2011-6-30'

表 'Distribute'。扫描计数 1，逻辑读取 3256 次，物理读取 0 次，预读0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 484 毫秒，占用时间= 7721 毫秒。

select\* from dbo.Distribute\_NoPartition where SendDate > '2011-6-1' and SendDate< '2011-6-30'

表 'Distribute\_NoPartition'。扫描计数 3，逻辑读取 38533 次，物理读取 459 次，预读 24007 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 1594 毫秒，占用时间 = 9603 毫秒

通过以上执行结果，可以看出使用了分区的查询时间为**7721** 毫秒，而未使用分区的也只有**9603** 毫秒，似乎区别不大。按设想把整个表分为了十二块，未使用分区的表查询需要全表扫描，而使用分区方案的表查询只需要查询十二块中的一块，理论上应分区应只需要未分区时间的十二分之一。而我们再次查看另一个关键性能参数即**扫描次数**和**逻辑读取次数**，使用分区方案的表的查询扫描计数 1逻辑读取了3256 次，而未使用分区方案的表的扫描计数 **3**，逻辑读取 **38533** 次，所以从内存访问情况分析，基本符合我们最初的设想，而时间差异不大的原因是查询中数据量很大，SQL Server把结果呈现出来占用了大量时间。所以后面我们再次用一个查询验证它们性能差异，代码如下：

withDistribute\_CTE as

(

select \*,ROW\_NUMBER()  Over(order by ID desc) as rowid fromdbo.Distribute where SendDate > '2011-6-1' and SendDate < '2011-6-30'

)select\* from  Distribute\_CTE where rowidbetween 30 and 40;

表 'Distribute'。扫描计数 1，逻辑读取 35 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 325 毫秒，占用时间= 214 毫秒。

withDistribute\_NoPartition\_CTE as

(

select \*,ROW\_NUMBER()  Over(order by ID desc) as rowid fromdbo.Distribute\_NoPartition where SendDate > '2011-6-1' and SendDate <'2011-6-30'

)select\* from  Distribute\_NoPartition\_CTE whererowid between 30 and 40;

(11 行受影响)

表 'Distribute\_NoPartition'。扫描计数 1，逻辑读取 19297 次，物理读取 18 次，预读 10036 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 2313 毫秒，占用时间 = 2678 毫秒。

上述用了一个分页的查询，为简明清晰使用一个表格来对比它们之间性能差异：

<table> 
 <tbody> 
 <tr> 
 <td> 是否分区 </td> 
 <td> CPU 时间 </td> 
 <td> 扫描计数 </td> 
 <td> 逻辑读取 </td> 
 <td> 占用时间 </td> 
 </tr> 
 <tr> 
 <td> 使用分区查询 </td> 
 <td> 325 </td> 
 <td> 1 </td> 
 <td> 35 </td> 
 <td> 214 </td> 
 </tr> 
 <tr> 
 <td> 未分区查询 </td> 
 <td> 2313 </td> 
 <td> 1 </td> 
 <td> 19297 </td> 
 <td> 2678 </td> 
 </tr> 
 </tbody> 
</table>

对比以上数据后，我们很清晰的发现分区后性能有明显的提升，将近提升10倍左右，当然这与分区的数量和分区字段及提供给用户的查询条件有关，本方案正是利用了用户关心业务数据的时间范围来进行分区，所以能很好的解决性能问题。

** **

**2．2．5分区方案实现自动护维**

尽管做了详细的预先规划，分区方案可能已按照预期方式进行工作。但随着时间的推移分区方案将不能满足需求了。以上示例代码中定义的分区到2011年12月，现在假设到了2012年1月了，这时数据都会放入到最后一个分区中，且后面一直如此，这样就不能均匀的把数据分配到分区上了，所以需要能够在现在分区上扩展。这时只需要在原有分区方案基础上扩展一个分区即可，而该分区重复循环的利用已有的文件组，具体代码如下：

ALTERPARTITION SCHEME PSch\_Month NEXT USED FG2;

ALTERPARTITION FUNCTION PFun\_Month() SPLIT RANGE ('2012-2-1');

这时我们插入一条2012年的记录将会放入到新扩展的分区中了。

\--插入一条年的记录然后查看它所有的分区号

INSERTINTO \[dbo\].\[Distribute\] VALUES (10,99,98,100,101,102,200,'2012-1-1');

select\*, $partition.PFun\_Month(SendDate) as 分区号 from \[Distribute\] whereSendDate='2012-1-1'

上述代码虽然完成了分区的扩展，但更为智能的方法是通过一个后台作业自动完成这一功能，而不需要人为的干预，在作业中运行的过程代码如下：

\--改进后的维护过程

alterprocedure proc\_AutoChangePartition

(

@now datetime

)

begin

declare @startDate datetime;

declare @partitionDate datetime;

declare @sql varchar(500);

declare @lastPartitionNo int;

declare @currentPartitionNo int;

--set @now = GETDATE();

set @startDate = '2010-12-1';

--约定日期与分区的关系为从-12-1后每增加一个月份则分区号+1

--获取当前日期分区号

set @lastPartitionNo = (SELECT fanout FROMsys.partition\_functions WHERE \[name\] = 'PFun\_Month');

set @currentPartitionNo =DATEDIFF(MONTH,@startDate,@now);

if(@currentPartitionNo >@lastPartitionNo)

begin

set @sql = 'ALTER PARTITION SCHEMEPSch\_Month NEXT USED FG' + LTRIM(@currentPartitionNo%12);

exec(@sql);

set @partitionDate =ltrim(YEAR(@now)) + '-' + LTRIM(Month(@now)) + '-1';

ALTER PARTITION FUNCTIONPFun\_Month() SPLIT RANGE(@partitionDate);

end;

因为业务发生时间不超过当前时间，所以只需要每月定时运行该过程即可。该过程将会检查分区方案中是否有存放到当前月份的分区，如没有则创建一个，这样就可实现自动的实现分区了。

**3． 树型层次结构数据优化**

** **

**3．1 树型层次结构数据概述**

系统中的地区、客户、产品都存在着树型结构的层次关系。如地区有国家、省、市等层次，而存储这种结构通常用二维表的父/子字段的关系来表示。而最关键的是对这些数据的查询，经常需要查询出某个节点下属的所有节点，在SQL Server2000前只能使用递归方式，这种方式不但复杂且性能很差。SQL Server 2005开始支持的CTE（公共表表达式）从一定程序上方便了该工作的实现，但仍未解决根本问题，因为它仅仅只是简化了SQL语句的编写，没真正提高查找性能。SQL 2008的最新支持的hierarchyid类型让这个工作更加简化和高效，该类型其实是一个CLR（公共语言运行时）自定义数据类型。以下通过实例来说明传统方式（CTE）和新技术方式（hierarchyid）的性能差异。

**3．2 两种方案性能对照**

首先分别创建两种解决树型结构数据存储的方案，这里以地区表为例，第一种方式是父/子两字段表示层次结构记录，第二种是采用最新支持的hierarchyid类型来表示这种层次关系，代码如下：

IFNOT EXISTS (SELECT \* FROM sys.objects WHERE object\_id =OBJECT\_ID(N'\[dbo\].\[Area\]') AND type in (N'U'))

BEGIN

CREATETABLE \[dbo\].\[Area\](

\[ID\] \[int\] NOT NULL,

\[Tree\_ID\] \[hierarchyid\] NOT NULL,

\[AreaName\] \[nvarchar\](50) NOT NULL,

CONSTRAINT \[PK\_Area\] PRIMARY KEY CLUSTERED

(

\[ID\] ASC

)WITH(PAD\_INDEX  = OFF,STATISTICS\_NORECOMPUTE  = OFF,IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS  =ON, ALLOW\_PAGE\_LOCKS  = ON) ON \[PRIMARY\]

) ON\[PRIMARY\]

END

IFNOT EXISTS (SELECT \* FROM sys.objects WHERE object\_id =OBJECT\_ID(N'\[dbo\].\[Area\_NoTree\]') AND type in (N'U'))

BEGIN

CREATETABLE \[dbo\].\[Area\_NoTree\](

\[ID\] \[int\] NOT NULL,

\[ParentID\] \[int\] NOT NULL,

\[AreaName\] \[nvarchar\](50) NOT NULL,

CONSTRAINT \[PK\_Area\_NoTree\] PRIMARY KEYCLUSTERED

(

\[ID\] ASC

)WITH(PAD\_INDEX  = OFF,STATISTICS\_NORECOMPUTE  = OFF,IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS  =ON, ALLOW\_PAGE\_LOCKS  = ON) ON \[PRIMARY\]

) ON\[PRIMARY\]

END

使用以下代码导入测试数据

--生成模拟地区数据

truncatetable \[Area\];

\--一级

INSERTINTO \[dbo\].\[Area\]

(\[ID\]

,\[Tree\_ID\]

,\[AreaName\])

values

(

0,'/',N'根地区名'

);

declare@i int,

@Leve1cnt int,

@Leve2cnt int,

@Leve3cnt int;

set@Leve1cnt = 10;

set@Leve2cnt = 50;

set@Leve3cnt = 250;

set@i = 1;

while(@i<= @Leve1cnt)

begin

INSERT INTO \[dbo\].\[Area\]

(\[ID\]

,\[Tree\_ID\]

,\[AreaName\])

values

(

@i,'/'+ LTRIM(@i) + '/',N'一级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

\--二级

set@i = 1;

while(@i<= @Leve2cnt)

begin

INSERT INTO \[dbo\].\[Area\]

(\[ID\]

,\[Tree\_ID\]

,\[AreaName\])

values

(

@i + @Leve1cnt,'/' +ltrim(@i%@Leve1cnt+1) + '/' + LTRIM(@i/@Leve1cnt+1) + '/',N'二级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

\--三级

set@i = 1;

while(@i<= @Leve3cnt)

begin

INSERT INTO \[dbo\].\[Area\]

(\[ID\]

,\[Tree\_ID\]

,\[AreaName\])

values

(

@i + @Leve2cnt + @Leve1cnt,'/' +ltrim(@i/(@Leve3cnt/@Leve1cnt)+1) + '/' +LTRIM(@i/(@Leve2cnt/@Leve1cnt)%(@Leve2cnt/@Leve1cnt)+1) + '/' +LTRIM(@i%(@Leve2cnt/@Leve1cnt)+1) + '/',N'三级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

\--生成模拟地区数据

\--一级

truncatetable \[Area\_NoTree\];

declare@Leve1cnt int,@i int;

set@Leve1cnt = 10;

set@i = 1;

while(@i<= @Leve1cnt)

begin

INSERT INTO \[dbo\].\[Area\_NoTree\]

(\[ID\]

,\[ParentID\]

,\[AreaName\])

values

(

@i,0,N'一级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

\--二级

declare@Leve2cnt int;

set@Leve2cnt = 50;

set@i = 1;

while(@i<= @Leve2cnt)

begin

INSERT INTO \[dbo\].\[Area\_NoTree\]

(\[ID\]

,\[ParentID\]

,\[AreaName\])

values

(

@i + @Leve1cnt,@i%@Leve1cnt + 1,N'二级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

\--三级

declare@Leve3cnt int;

set@Leve3cnt = 150;

set@i = 1;

while(@i<= @Leve3cnt)

begin

INSERT INTO \[dbo\].\[Area\_NoTree\]

(\[ID\]

,\[ParentID\]

,\[AreaName\])

values

(

@i + @Leve2cnt +@Leve1cnt,@i%@Leve2cnt + 1,N'三级地区名' + LTRIM(@i)

);

set @i = @i + 1;

end;

完成模拟数据插入后，就可以时行具体的测试了。

\--某节点下的所有子节点

DECLARE@tree\_ID hierarchyid;

SELECT@tree\_ID=tree\_ID FROM area WHERE id=7;

SELECT\*,tree\_ID.GetLevel()AS Level FROM area WHERE tree\_ID.IsDescendantOf(@tree\_ID)=1

(31 行受影响)

表 'Area'。扫描计数 1，逻辑读取4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 0 毫秒，占用时间 =0 毫秒。

withcte

(

select ID,parentid,areaname from area\_notreewhere ID=1

union all

select A.ID,A.parentid,A.areaname fromarea\_notree A join cte C on A.parentID = C.ID

)

select\* from cte;

(31 行受影响)

表 'Worktable'。扫描计数 2，逻辑读取 187 次，物理读取 0 次，预读 0次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

表 'Area\_NoTree'。扫描计数 1，逻辑读取 126 次，物理读取 0 次，预读 0次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:

CPU 时间 = 6 毫秒，占用时间 =7 毫秒。

上述使用了一个典型查找某节点下所有子节点的查询，为简明清晰使用一个表格来对比它们之间性能差异：

对比以上数据后，使用新技术查询时所花费时间几乎为0。

**3．3 hierarchyid 数据类型深入分析**

通过以上实验例子可以看出使用hierarchyid 数据类型来表现树型结构在性能上的优异性。下面将详细分析该类型的特点。

l  非常紧凑

在具有 *n* 个节点的树中，表示一个节点所需的平均位数取决于平均端数（节点的平均子级数）。端数较小时 (0-7)，大小约为 6\*logA*n* 位，其中 A 是平均端数。对于平均端数为 6 级、包含 100,000 个人的组织层次结构，一个节点大约占 38 位。存储时，此值向上舍入为 40 位，即 5 字节。

l  按深度优先顺序进行比较

给定两个 hierarchyid 值 **a** 和 **b**，**a<b** 表示在对树进行深度优先遍历时，先找到 a，后找到 b。hierarchyid 数据类型的索引按深度优先顺序排序，在深度优先遍历中相邻的节点的存储位置也相邻。例如，一条记录的子级的存储位置与该记录的存储位置是相邻的。

l  支持任意插入和删除

通过使用 [ GetDescendant][GetDescendant]方法，始终可以在任意给定节点的右侧、左侧或任意两个同级节点之间生成同级节点。在层次结构中插入或删除任意数目的节点时，该比较属性保持不变。大多数插入和删除操作都保留了紧凑性属性。但是，对于在两个节点之间执行的插入操作，所产生的 hierarchyid 值的表示形式在紧凑性方面将稍微降低。

**3．4结论**

通过以上实例对比和内部的原理机制分析，不难看出本系统中的树型结构的数据完全适合于使用该方案来解决，并可保证获得优异性能。

** **** **

**4． 报表性能优化**

**4．1 本系统报表的特点分析**

报表是本系统中最为重要的一部分，它对系统中各类数据统计展示，为各种决策提供依据。但由于系统复杂、数据量大，一个报表往往需要关联好几个表或视图，按常规查询或只作索引优化无法解决根本问题，时常会造成一个报表需要数分钟或更长的等待时间才能展现给用户，甚至经常出现超时情况。这样会降低系统的用户体验和可用性，另外使用该功能的多般为公司主要领导，应尽可能的达到好的用户体验，而体验最重要的指标之一就是快捷。以下分析的都是指业务表中数据量很大，且需要关联多个业务，还需要经过大量过滤和分组统计才能得出最终结果的报表。

** **

**4．2 实现方案分析**

对于查询业务复杂而且查询的表及条件过多时，报表查询的性能大大下降，因为这会涉及到大量的表关联、分组统计、过滤等操作，其中对性能影响最大的就是多表之间的联接(join)和分组(Group by)统计。而用户输入的查询条件进行过虑可通过索引来解决，所以如果能把每次联接和统计这部分工作事先完成则可解决该性能问题。为了便于问题的分析，下面列出具体的一个报表场景，为了描述方便仅对其关键字段时行描述。

报表将要展示样例如下，即需要了解某地区某种型号的设备在某一日期发生的所有业务数量，这些业务有发货、调入、调出、零售，如下表所示：

但上述的各种业务发生的数量都单独存放在各自的表中，以下仅列出其中的发货表样例，其它表类似。

如上述分析一致，如果按常规的方式（用JOIN关联）将至少关联5张表，系统开销非常大，并且所编写的语句非常复杂，本人使用UNION ALL后再统计（sum求和）的方法，各业务表的数据量为1000万时，查询分析器耗费近5（5\*60\*1000毫秒）分钟，才计算出结果，可见此方法不可能被用户所接受，通过查看其实际的执行计划发现，有大量的关联（JOIN）操作占了绝大部分开销，按上述分析需要解决这一性能瓶颈。通常的做法把所需要的全体数据一次查找出来放入到另一个专供报表访问的表中，简称该表为报表表，然后在该表上为经常查询的且选择度较高的字段加上索引，另外为了能更新原始业务表中的变化（新插入或更新了记录等）到报表表中，需要通过一个作业定时查询并更新到报表表中。但由于不能确定那些记录更新了或是新插入（这里没有删除，因为业务数据不充许删除）的所以每次需要把已经统计好的报表记录全部删除，然后关联多个表查询出所有记录插入到报表表中。显然该方案可以解决多表查询时大量的联接操作，如果频繁的运行该作业也可以保证数据有较好的实时性，通常可以满足报表的需求，因为绝大多数场景下报表数据是允许一定延时的，但存在以下不足之处：

l  业务表不管改变是否巨大，或者跟本就没有记录更新，但作业仍会全量统计所有数据，这明显示浪费了系统资源；

l  各个业务表数据量越来越大时，每次作业会删除和重新生成的数据越来越多，系统负担越来越大，甚至超时；

l  当要求频繁运行该作业或需要在业务繁忙时更新报表数据将变得不可能，因为系统已有大量用户使用情况下运行一个笨重的作业会影响前台用户操作；

l  在删除报表数据后，到全部插入完成这段时间内用户的任何报表请求都无法查询，处于等待或超时状态。

基于以上分析，通过作业的方式频繁的更新数据到报表表中，唯一不能解决的问题是增量更新，即在业务表中大量的数据在某一段时间相对稳定的，只会有少量的数据做了更新，在一小段时间内一般也只有少量的新增记录，如果能够得到这些数据，那么就可以在原有的报表表上只更新和插入这些数据将变得非常“轻量级” 。但是要找出这批记录是非常困难的，常规的方式是记录日志，即只要业务数据变化都写一条日志，然后依据日志来获取变更的业务数据，但业务表很多，对其操作又分很多种类（如更新、删除、审批等），该方案需要大量的人力投入且容易出现差错，保障性差，统计复杂，所以需要更为先进的方案，接下来将详细的分析通过加入rowversion数据类型来解决这一问题。

**4．3 rowversion数据类型分析**

经过分析和网上查找发现rowversion类型字段恰好适用这一场景，能解决上述增量更新问题。每个数据库都有一个计数器，当对数据库中包含 rowversion 列的表执行插入或更新操作时，该计数器值就会增加。此计数器是数据库行版本。这样就可以跟踪数据库内的相对时间，而不是时钟相关联的实际时间。一个表只能有一个rowversion 列。每次修改或插入包含 rowversion 列的行时，就会在 rowversion 列中插入经过增量的数据库行版本值。通过这一列值就很容易的得到表中更新和新增的记录了，接下来将详细分析怎么在作业中使用行版本来增量更新报表数据。

** **

**4．3方案具体实施**

通过以上分析后，已经有了解决问题的基本思路了，下面重点以发货业务为代表来分析该方案的具体实施。下方为作业具体实现的流程图：

![Image 1][]

对照以上流程图的说明如下：

1.     获取上次行版本

如果作业是首次运行，则无法获取上次运行后的行版本值，这时取出的是空值，在接下来的步骤中要做全量更新，则全量更新后直接跳到步骤5；而如果不是首次运行，则在上次更新完数据后会记录一个最新的行版本值。

2. 依据行版本查找出变更的记录

因为上次运行完成后可以保证各业务表与报表表是同步的，而这时产生的行版本值之前的记录是已同步过的，但作业完成后的更新的记录都会比该行版本值大。所以查找这部分记录变得非常容易，只需判断该表中那个记录的行版本比上次的值大即可，可以把这部分记录保存到临时表中。

3. 从报表表中删除已变更记录

如果业务表中的记录只做了更新操作，那么这部分记录之前已经同步到报表表中了，所以要先删除这部分记录。

4. 插入变更和新增加记录