希望各位大虾帮我看看这个视图该怎么去优化，速度越快越好，万分感谢。

Question

CREATE view [dbo].[kqychzb_pro]
AS
select b01.Dept_Code as 部门,COUNT(K_Month.a0188) as 部门总人数,
(sum(isnull(leave_time10,0))+sum(isnull(leave_time11,0))+
sum(isnull(leave_time12,0))+sum(isnull(leave_time13,0))+
sum(isnull(leave_time14,0))+sum(isnull(leave_time15,0))+
sum(isnull(leave_time16,0))+sum(isnull(leave_time17,0))+
sum(isnull(leave_time18,0))+sum(isnull(leave_time19,0))+
sum(isnull(leave_time20,0))+sum(isnull(leave_time27,0))+
sum(isnull(leave_time28,0))+sum(isnull(leave_time29,0))+
sum(isnull(leave_time30,0))+sum(isnull(leave_time31,0))+
sum(isnull(leave_time32,0))+sum(isnull(leave_time9,0))) as 请假天数,
sum(isnull(OutDays,0)) as 出差天数,    
sum(isnull(HasTiaoXiuDays,0)) as 调休天数,
sum(case when (card_end is null and card_begin is null) then 2
     when (card_end is null and card_begin is not null) then 1
     when (card_end is not null and card_begin is null) then 1
     when (card_end is not null and card_begin is not null) then 0 end) as 忘刷卡次数,
sum(isnull(k_month.LATE_MIN,0)) as 迟到分钟,    
sum(isnull(k_month.EARLY_MIN,0)) as 早退分钟,
sum(isnull(k_month.absent_time,0)) as 旷工小时,
 sum(isnull(LATE_COUNT,0)) as 迟到人次,
sum(isnull(EARLY_COUNT,0)) as 早退人次, 
sum(isnull(k_month.ABSENT_COUNT,0)) as 旷工人次,
sum(isnull(over_time6,0)) as 行政加班小时数,
sum(isnull(over_time3,0)) as 周末加班小时数,
sum(isnull(over_time5,0)) as 法定加班小时数from a01,b01,K_MONTH,K_Day where a01.Dept_Code=b01.DEPT_CODE and 
 k_day.a0188=k_month.a0188 and convert(varchar(6),k_day.DUTY_DATE,112)=k_month.GZ_YM and  A01.A0188=K_Month.a0188 
  group by b01.Dept_Code 
union
 select b01.Dept_Code as 部门,COUNT(k_month_History.a0188) as 部门总人数,
(sum(isnull(leave_time10,0))+sum(isnull(leave_time11,0))+
sum(isnull(leave_time12,0))+sum(isnull(leave_time13,0))+
sum(isnull(leave_time14,0))+sum(isnull(leave_time15,0))+
sum(isnull(leave_time16,0))+sum(isnull(leave_time17,0))+
sum(isnull(leave_time18,0))+sum(isnull(leave_time19,0))+
sum(isnull(leave_time20,0))+sum(isnull(leave_time27,0))+
sum(isnull(leave_time28,0))+sum(isnull(leave_time29,0))+
sum(isnull(leave_time30,0))+sum(isnull(leave_time31,0))+
sum(isnull(leave_time32,0))+sum(isnull(leave_time9,0))) as 请假天数,
sum(isnull(OutDays,0)) as 出差天数,    sum(isnull(HasTiaoXiuDays,0)) as 调休天数,
sum(case when (card_end is null and card_begin is null) then 2
     when (card_end is null and card_begin is not null) then 1
     when (card_end is not null and card_begin is null) then 1
     when (card_end is not null and card_begin is not null) then 0 end) as 忘刷卡次数,
sum(isnull(k_month_History.LATE_MIN,0)) as 迟到分钟,    sum(isnull(k_month_History.EARLY_MIN,0)) as 早退分钟,
sum(isnull(k_month_History.absent_time,0)) as 旷工小时, sum(isnull(LATE_COUNT,0)) as 迟到人次,
sum(isnull(EARLY_COUNT,0)) as 早退人次, sum(isnull(k_month_History.ABSENT_COUNT,0)) as 旷工人次,
sum(isnull(over_time6,0)) as 行政加班小时数,
sum(isnull(over_time3,0)) as 周末加班小时数,
sum(isnull(over_time5,0)) as 法定加班小时数from a01,b01,k_month_History,k_day_History where a01.Dept_Code=b01.DEPT_CODE 
and k_day_History.a0188=k_month_History.a0188 and 
convert(varchar(6),k_day_History.DUTY_DATE,112)=k_month_History.GZ_YM and A01.A0188=k_month_History.a0188 
group by b01.Dept_Code 
GO次段代码速度很慢，希望大家给点意见！

Answer 1

代码1
union
代码2
挑出慢的
怎么没有时间段限制的条件

Answer 2

K_MONTH,K_Day跟历史表先union放到临时表里，在连接a01,b01，计算试试，新手建议

Answer 3

convert(varchar(6),k_day_History.DUTY_DATE,112)=k_month_History.GZ_YM 这样关联会很慢吧,试试减少数据量和增加索引的办法...

Answer 4

#1. 可去掉ISNULL，SUM()会自动过滤NULL字段.如果和有NULL的情况，就改成ISNULL(SUM(), 0)
#2. 如果两个表中的数据不重复，则用UNION ALL