首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

【好手来】优化索引或语句

2013-07-08 
【高手来】优化索引或语句。如何优化？ SELECT sum( CASEWHEN Hours.Status2 THEN Detail.TimeSpaneWHEN Hour

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


 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






[解决办法]
WfWorkLogDetail是聚集索引扫描（跟表扫描差不多了），你聚集索引建在ID上，而查询条件上是StartTime
没有用到索引（Seek），所以建议把聚集索引修改为StartTime所在的列上
[解决办法]
 
ALTER TABLE [dbo].[WfWorkLogDetail] DROP CONSTRAINT [PK_WfWorkLogDetail]
 
create clustered index index_Startdate on [WfWorkLogDetail]([StartTime])

[解决办法]
另外就是在StartTime建立聚集索引后，
生成
datepart(month,Detail.StartTime) AS Months,
datepart(year,Detail.StartTime) AS Years
就直接从索引中获取了，感觉这个索引对查询还有生成结果都有利

不知道你第二个表的Status选择性高不高
从执行计划看，性能主要花费在这个表的处理上，你同样是一个聚集索引扫描（Scan），也一样没有在查询条件上用到索引。
[解决办法]
status这个列估计分布率不高，

还有，你这个语句执行时间是多长，两个表的数据是多少？

读书人精选
热点排行