以下语句执行效率很低,请高手支招看如何改写比较快,其中有两个参数 @in_fmdate 起始日期,@in_todate 截止日期
select a.rec_incod,f.atd_date,a.rec_date,a.rec_cno
into #tmpdoor
from #tmpdoor0 a,process e,attend f
where e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod
and e.pro_incod=f.atd_incod and e.pro_date=f.atd_date
and ((a.rec_date between (case when e.pro_tm01>f.atd_tm01 then e.pro_tm01 else f.atd_tm01 end)
and (case when e.pro_tm02>f.atd_tm02 and F.atd_tm02<>'' then f.atd_tm02 else e.pro_tm02 end)
and (f.atd_tm01<>'' or f.atd_tm02<>''))
or (a.rec_date between (case when e.pro_tm03>f.atd_tm03 then e.pro_tm03 else f.atd_tm03 end)
and (case when e.pro_tm04>f.atd_tm04 and f.atd_tm04<>'' then f.atd_tm04 else e.pro_tm04 end)
and (f.atd_tm03<>'' or f.atd_tm04<>''))
or (a.rec_date between case
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)<f.atd_ot01 then f.atd_ot01
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)>=f.atd_ot01 then dateadd(hour,1,e.pro_tm02)
when e.pro_tm03<>'' and dateadd(hour,1,e.pro_tm04)<f.atd_ot01 then f.atd_ot01
else dateadd(hour,1,e.pro_tm04) end
and f.atd_ot02 and (f.atd_ot02<>'' and f.atd_ot01<>'')))
select a.rec_incod,f.atd_date,a.rec_date,a.rec_cno
into #tmpdoor
from #tmpdoor0 a,process e,attend f
where e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod
and e.pro_incod=f.atd_incod and e.pro_date=f.atd_date
and ((a.rec_date between (case when e.pro_tm01>f.atd_tm01 then e.pro_tm01 else f.atd_tm01 end)
and (case when e.pro_tm02>f.atd_tm02 and F.atd_tm02<>'' then f.atd_tm02 else e.pro_tm02 end)
and (f.atd_tm01<>'' or f.atd_tm02<>''))
or (a.rec_date between (case when e.pro_tm03>f.atd_tm03 then e.pro_tm03 else f.atd_tm03 end)
and (case when e.pro_tm04>f.atd_tm04 and f.atd_tm04<>'' then f.atd_tm04 else e.pro_tm04 end)
and (f.atd_tm03<>'' or f.atd_tm04<>''))
or (a.rec_date between case
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)<f.atd_ot01 then f.atd_ot01
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)>=f.atd_ot01 then dateadd(hour,1,e.pro_tm02)
when e.pro_tm03<>'' and dateadd(hour,1,e.pro_tm04)<f.atd_ot01 then f.atd_ot01
else dateadd(hour,1,e.pro_tm04) end
and f.atd_ot02 and (f.atd_ot02<>'' and f.atd_ot01<>'')))
解决方案 »
- 如何查看某个表何时被改动了
- 两表合并查询
- 我在建存诸过程中,declare @aa numeric,set @sql='select @aa=a from b' exec (@sql),竟出错了说@aa 没有定义呀, 注:@sql 一定要是字符串
- 读取文本数据,列字符串长度大于255出错
- 有关整数与小数小问题
- 问一个 SQL 找重复数据的问题
- 怎样将SQL Server 7.0的数据库备份到局域网中其他机器的硬盘上吗?
- 关于远程登陆,sp_addlinkedsrvlogin???
- 同步的问题(拒绝访问)
- 两台SQL服务器同步条件表
- 关于嵌套查询,怎么提示order附近有语法错误?
- update 语句
process的pro_date、pro_incod有无索引
attend 的atd_date有无索引
rec_incod,rec_date,rec_cno
卡号 ,刷卡日期,卡机号process 排班表
pro_tm01,pro_tm02,pro_tm03,pro_tm04
上班一, 下班一, 上班二, 下班二attend 出勤日报表
atd_tm01,atd_tm02,atd_tm03,atd_tm04,atd_ot01,atd_ot02
上班一, 下班一, 上班二, 下班二, 加班一, 加班二数据太多了,没法提供,帮我看一下那句写法是否可以优化,该怎么写上面功能
process的Pro_incod有索引
attend的atd_incod,atd_date有索引
相信LZ肯定可以优化的出来
加油!
into #tmpdoor
from #tmpdoor0 a,process e,attend f
where e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod
and e.pro_incod=f.atd_incod and e.pro_date=f.atd_date
and (a.rec_date between (case when e.pro_tm01>f.atd_tm01 then e.pro_tm01 else f.atd_tm01 end)
and (case when e.pro_tm02>f.atd_tm02 and F.atd_tm02<>'' then f.atd_tm02 else e.pro_tm02 end)
and (f.atd_tm01<>'' or f.atd_tm02<>''))
UNION ALL
select a.rec_incod,f.atd_date,a.rec_date,a.rec_cno
from #tmpdoor0 a,process e,attend f
where e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod
and e.pro_incod=f.atd_incod and e.pro_date=f.atd_date
AND (a.rec_date between (case when e.pro_tm03>f.atd_tm03 then e.pro_tm03 else f.atd_tm03 end)
and (case when e.pro_tm04>f.atd_tm04 and f.atd_tm04<>'' then f.atd_tm04 else e.pro_tm04 end)
and (f.atd_tm03<>'' or f.atd_tm04<>''))
UNION ALL
select a.rec_incod,f.atd_date,a.rec_date,a.rec_cno
into #tmpdoor
from #tmpdoor0 a,process e,attend f
where e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod
and e.pro_incod=f.atd_incod and e.pro_date=f.atd_date
AND (a.rec_date between case
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)<f.atd_ot01 then f.atd_ot01
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)>=f.atd_ot01 then dateadd(hour,1,e.pro_tm02)
when e.pro_tm03<>'' and dateadd(hour,1,e.pro_tm04)<f.atd_ot01 then f.atd_ot01
else dateadd(hour,1,e.pro_tm04) end
and f.atd_ot02 and (f.atd_ot02<>'' and f.atd_ot01<>''))
改成f.atd_date between @in_fmdate and @in_todate
select a.rec_incod,f.atd_date,a.rec_date,a.rec_cno into #tmpdoor
from #tmpdoor0 a,process e,attend fwhere e.pro_date between @in_fmdate and @in_todate
and a.rec_incod=e.pro_incod and e.pro_incod=f.atd_incod and e.pro_date=f.atd_dateand
(
(
a.rec_date between (case when e.pro_tm01>f.atd_tm01 then e.pro_tm01 else f.atd_tm01 end) and
(case when e.pro_tm02>f.atd_tm02 and F.atd_tm02<>'' then f.atd_tm02 else e.pro_tm02 end)
and (f.atd_tm01<>'' or f.atd_tm02<>'')
)
or
(
a.rec_date between (case when e.pro_tm03>f.atd_tm03 then e.pro_tm03 else f.atd_tm03 end) and
(case when e.pro_tm04>f.atd_tm04 and f.atd_tm04<>'' then f.atd_tm04 else e.pro_tm04 end)
and (f.atd_tm03<>'' or f.atd_tm04<>'')
)
or
(
a.rec_date between (case
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)<f.atd_ot01 then f.atd_ot01
when e.pro_tm03='' and dateadd(hour,1,e.pro_tm02)>=f.atd_ot01 then dateadd(hour,1,e.pro_tm02)
when e.pro_tm03<>'' and dateadd(hour,1,e.pro_tm04)<f.atd_ot01 then f.atd_ot01
else dateadd(hour,1,e.pro_tm04) end) and
f.atd_ot02
and (f.atd_ot02<>'' and f.atd_ot01<>'')
)
)
--把这句
e.pro_date between @in_fmdate and @in_todate
--换成
e.pro_date >= @in_fmdate and e.pro_date<=@in_todate