【高手来】优化索引或语句。

[吕津]

如何优化？

 SELECT sum( CASE 
                              WHEN Hours.Status=2 THEN Detail.TimeSpane
                              WHEN Hours.Status=4 THEN Detail.TimeSpane*2
                              ELSE 0 END) AS TimeSpane,Detail.Submitter,
                              
                    datepart(month,Detail.StartTime) AS Months,
                    datepart(year,Detail.StartTime) AS Years

             FROM   WfWorkLogDetail AS Detail
             JOIN   WorkingHour AS Hours
               ON   Detail.StartTime> = Hours.StartTime			   
             AND   Detail.StartTime<Hours.EndTime                         
              WHERE  Hours.Status = 2
                OR  Hours.Status = 4
             GROUP BY
                    Detail.Submitter,
                    datepart(month,Detail.StartTime),
                    datepart(year,Detail.StartTime)

USE [hamp]
GO
/****** 对象:  Table [dbo].[WfWorkLogDetail]    脚本日期: 07/04/2013 12:56:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WfWorkLogDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[WfWorkLogID] [int] NOT NULL,
	[WorkTypeID] [int] NOT NULL,
	[ProjectID] [int] NULL,
	[ProjectName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[AddDate] [datetime] NOT NULL,
	[WorkDate] [smalldatetime] NULL,
	[StartTime] [smalldatetime] NOT NULL,
	[EndTime] [smalldatetime] NOT NULL,
	[TimeSpane] [int] NOT NULL,
	[Context] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
	[ResidualProblem] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
	[ResidualProblemReply] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
	[Suggest] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
	[SuggestReply] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NULL,
	[ReviewRemark] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
	[Difficulty] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Grade] [smallint] NULL,
	[ReviewDate] [smalldatetime] NULL,
	[Submitter] [int] NOT NULL,
	[Dept] [int] NULL,
	[Reviewer] [int] NOT NULL,
	[IsDeleted] [bit] NULL CONSTRAINT [DF_WfWorkLogDetail_IsDeleted]  DEFAULT ((0)),
	[IsReviewed] [bit] NULL CONSTRAINT [DF_WfWorkLogDetail_IsReviewed]  DEFAULT ((0)),
	[Status] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
	[ManageRemark] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_WfWorkLogDetail] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识列' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主表（WfWorkLog）标识列' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'WfWorkLogID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作类型' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'WorkTypeID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目编号（标识列）' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ProjectID'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录添加时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'AddDate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作完成日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'WorkDate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开始时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'StartTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结束时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'EndTime'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'时长，单位分钟' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'TimeSpane'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日志内容' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Context'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'遗留问题' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ResidualProblem'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核人对遗留问题的答复' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ResidualProblemReply'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建议' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Suggest'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核人对建议的回复' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'SuggestReply'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核人对日志的评价' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ReviewRemark'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作难度' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Difficulty'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'评分' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Grade'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ReviewDate'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日志提交人' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Submitter'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Dept'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'审核人' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Reviewer'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否已删除' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'IsDeleted'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否已审核' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'IsReviewed'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'此条记录的状态，“S”代表被保存的记录；“P”代表审核中的记录' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'Status'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用于手动修改数据时的备注（管理员用）' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'WfWorkLogDetail', @level2type=N'COLUMN', @level2name=N'ManageRemark'

USE [hamp]
GO
/****** 对象:  Table [dbo].[WorkingHour]    脚本日期: 07/04/2013 12:56:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WorkingHour](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[StartTime] [datetime] NOT NULL,
	[EndTime] [datetime] NOT NULL,
	[Status] [char](1) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_WorkingHour] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

[吕津]

执行计划：

[吕津]

索引假脱机： http://www.cnblogs.com/lyhabc/archive/2013/04/19/3029840.html

[Leon_He2014]

楼主你都加了些什么索引呢

[专注or全面]

WfWorkLogDetail是聚集索引扫描（跟表扫描差不多了），你聚集索引建在ID上，而查询条件上是StartTime 没有用到索引（Seek），所以建议把聚集索引修改为StartTime所在的列上

[专注or全面]

 
ALTER TABLE [dbo].[WfWorkLogDetail] DROP CONSTRAINT [PK_WfWorkLogDetail]
 
create clustered index index_Startdate on [WfWorkLogDetail]([StartTime])

[专注or全面]

另外就是在StartTime建立聚集索引后， 生成 datepart(month,Detail.StartTime) AS Months, datepart(year,Detail.StartTime) AS Years 就直接从索引中获取了，感觉这个索引对查询还有生成结果都有利 不知道你第二个表的Status选择性高不高 从执行计划看，性能主要花费在这个表的处理上，你同样是一个聚集索引扫描（Scan），也一样没有在查询条件上用到索引。

[最爱午夜]

status这个列估计分布率不高， 还有，你这个语句执行时间是多长，两个表的数据是多少？

[专注or全面]

不吭不嗯的就结贴了，问题到底是怎么解决的啊？