SQL优化的典型性能案例

Question

我有一个SQL优化问题的工程实践，我认为这是一个典型的案例，会对很多人有帮助。

SQL SERVER 2005，

首先，创建主表。这是一个人员信息表。

CREATE TABLE [dbo].[OLAPAgentDim](
    [RoleID] [varchar](50) NULL CONSTRAINT [DF_OLAPAgentDim_RoleID]  DEFAULT ((1)),
    [OLAPKey] [bigint] IDENTITY(1,1) NOT NULL,
    [FatherKey] [bigint] NULL,
    [FatherKeyValue] [nvarchar](100) NULL,
    [System] [varchar](6) NULL,
    [Level] [int] NULL,
    [IfLeaf] [real] NULL,
    [IfDel] [real] NULL CONSTRAINT [DF_OLAPAgentDim_IfDel]  DEFAULT ((0)),
    [SourceKey] [varchar](50) NULL,
    [MainDemoName] [nvarchar](100) NULL,
    [FastCode] [varchar](50) NULL,
    [TagValue] [varchar](50) NULL,
    [Script] [nvarchar](max) NULL,
    [Birthday] [datetime] NULL,
    [EarlyStartTime] [datetime] NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [EditTime] [datetime] NULL,
    [BecomesTime] [datetime] NULL,
    [ContractTime] [datetime] NULL,
    [ContractEndTime] [datetime] NULL,
    [XMLIcon] [nvarchar](max) NULL,
    [PassKey] [varchar](50) NULL CONSTRAINT [DF_OLAPAgentDim_PassKey]  DEFAULT ('N3pkY3RHaeZXA9mGJdfm8A=='),
    [Address] [nvarchar](100) NULL,
    [HomeTel] [varchar](50) NULL,
    [Mobile] [varchar](50) NULL,
    [Email] [varchar](100) NULL,
    [IDCard] [varchar](50) NULL,
    [IDSecu] [varchar](50) NULL,
    [IDEndowment] [varchar](50) NULL,
    [IDAccumulation] [varchar](50) NULL,
    [ContactPerson] [nvarchar](100) NULL,
    [ContactPersonTel] [varchar](50) NULL,
    [Others1] [varchar](50) NULL,
    [SexKey] [varchar](2) NULL CONSTRAINT [DF_OLAPAgentDim_SexKey]  DEFAULT ((1)),
    [SexKeyValue] [nvarchar](100) NULL,
    [MarrageKey] [varchar](2) NULL CONSTRAINT [DF_OLAPAgentDim_MarrageKey]  DEFAULT ((1)),
    [MarrageKeyValue] [nvarchar](100) NULL,
    [Nation] [nvarchar](50) NULL,
    [Race] [nvarchar](50) NULL,
    [PartyMemberKey] [varchar](2) NULL CONSTRAINT [DF_OLAPAgentDim_PartyMemberKey]  DEFAULT ((1)),
    [PartyMemberKeyValue] [nvarchar](100) NULL,
    [RegionKey] [bigint] NULL CONSTRAINT [DF_OLAPAgentDim_RegionKey]  DEFAULT ((1)),
    [RegionKeyValue] [nvarchar](100) NULL,
    [LeaveResonKey] [bigint] NULL CONSTRAINT [DF_OLAPAgentDim_LeaveResonKey]  DEFAULT ((1)),
    [LeaveResonKeyValue] [nvarchar](100) NULL,
    [RoleStr] [varchar](max) NULL,
    [RoleStrValue] [nvarchar](max) NULL,
    [LeaderKey] [bigint] NULL CONSTRAINT [DF_OLAPAgentDim_LeaderKey]  DEFAULT ((1)),
    [LeaderKeyValue] [nvarchar](100) NULL,
    [FastCode2] [varchar](50) NULL,
    [FastCode3] [varchar](50) NULL,
    [FastCode4] [varchar](50) NULL,
    [FastCode5] [varchar](50) NULL,
    [OtherAddress] [nvarchar](100) NULL,
    [ShowOrder] [int] NULL,
    [RaceKey] [bigint] NULL DEFAULT ((1)),
    [RaceKeyValue] [nvarchar](100) NULL,
    [DepartLevelKey] [bigint] NULL DEFAULT ((1)),
    [DepartLevelKeyValue] [nvarchar](100) NULL,
    [forumname] [nvarchar](100) NULL,
    [IfCloseKey] [bigint] NULL DEFAULT ((1)),
    [IfCloseKeyValue] [nvarchar](100) NULL,
    [InsureStartTime] [datetime] NULL,
    [AccumulationStartTime] [datetime] NULL,
    [Rate] [varchar](50) NULL,
    [DirectLeaderKey] [bigint] NULL CONSTRAINT [DF_OLAPAgentDim_DirectLeaderKey]  DEFAULT ((1)),
    [DirectLeaderAttriKey] [bigint] NULL CONSTRAINT [DF_OLAPAgentDim_DirectLeaderAttriKey]  DEFAULT ((1)),
    [DirectLeaderKeyValue] [nvarchar](100) NULL,
    [DirectLeaderSourceKey] [varchar](50) NULL,
    [DirectLeaderPartName] [nvarchar](100) NULL,
    [DirectLeaderPositionName] [nvarchar](100) NULL,
    [NOTSync] [int] NULL,
    [FatherPath] [nvarchar](max) NULL,
    [SaleDiscount] [real] NULL,
 CONSTRAINT [PK_OLAPAgent Dim] PRIMARY KEY CLUSTERED 
(
    [OLAPKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

其次，在表中插入大约10,000条记录。我认为10,000条记录对SQL SERVER来说不是一个很大的数字。你可以看到这实际上是一个父子维度表。带有ifleaf=0的记录表示人员的部门结构节点，带有ifleaf=1的记录表示人员。您可以使用FahterKey列定义父子关系。例如：

OLAPKey IfLeaf FatherKey DepartLevelKey  MainDemoName 
  2        0      0           1          IBM Company
  3        0      2           2          Sales Depart    
  4        0      2           2          Service Depart
  5        0      3           3          Sales Team1  
  6        1      5          NULL        John Smith
  7        1      4          NULL        Mary
......

DepartLevelKey列表示离开节点的级别。因此，在此表中，我们可以保存整个HR树信息。

第三，我们看到了SQL问题：

create table #t
(
TableID int IDENTITY(1,1),
OLAPKey bigint,
MainDemoName nvarchar(max)
)

declare @t4 table
(
TableID int IDENTITY(1,1),
MainDemoName nvarchar(max),
OLAPKeystr varchar(100)
)

declare @agentkey bigint
set @agentkey ='2'

    --Part A
    --DepartLevelKey=2, to get @agentkey node's all level=2 department

    ;WITH Result AS(
    SELECT OLAPKey,DepartLevelKey,maindemoname FROM OLAPAgentDim WHERE OLAPKey =@agentkey
     UNION ALL 
     SELECT a.OLAPKey,a.DepartLevelKey,a.maindemoname FROM  OLAPAgentDim AS a,Result AS b WHERE a.FatherKey = b.OLAPKey
    )

     insert #t select OLAPKey,maindemoname from Result where DepartLevelKey=4

    --Part B
    ;with One as  
    (  
    select *,convert(varchar(50),OLAPKey) as Re from #t  
    )
    insert @t4 select maindemoname,stuff((select ','+Re from One where One.maindemoname=#t.maindemoname for xml path('')),1,1,'') as Two  
    from #t 
    group by maindemoname  
    drop table #t

上面的SQL分为A部分和B部分。A部分SQL获取根节点下的所有子节点(并过滤属于指定DepartLevelKey的子节点)。例如，获取销售部的子部门中的所有人员的level=3。

B部分SQL将行更改为列，例如：

Change:
TableID  OLAPKey MainDemoName
  1        6     Sales Team1
  2        10    Sales Team1
  3        12    Sales Team1
to:
TableID  MainDemoName OLAPKeystr
  1      Sales Team1   6,10,12

因此，我们得到每个目标部门的人员，以便进一步处理(这里省略)。

问题:A部分非常慢，大约需要5分钟。B部分也很慢。

我想知道如何根据已有的表结构对其进行优化。

你的，伊凡

Answer 1

尝试：

(i)将此索引添加到OLAPAgentDim

create index IX_OLAPAgentDim_FatherKey on OLAPAgentDim (FatherKey) include (DepartLevelKey, MainDemoName)

(ii)将#t中的MainDemoName从nvarchar(max)更改为nvarchar(100)。这与OLAPAgentDim中的列定义相匹配。

(Iii)A部分和B部分之间的，即在A部分之后和B部分之前，将此索引添加到#t

create clustered index IX on #t (MainDemoName)