在被连接的各个表上的关联字段建立索引,试着将查询语句中几个相同功能的case when处理放在子查询中处理,而在查询中直接引用子查询处理的结果。另外:
--------------------------------------------------------------------------------
case
case
when left(stk.product,4) between '4000' and '4999' then [stock_length]
else ''
end when '' then ''
else dtm.order_qty*
(case
when left(stk.product,4) between '4000' and '4999' then [stock_length]
else ''
end)/12
end as Footage,
==>
case
when left(stk.product,4) between '4000' and '4999' and stock_length!=''
then dtm.order_qty*[stock_length]/12
else ''
end AS Footage,
----------------------------------------------------------------------------------
(year(hdm.date_entered) between year(getdate())-1 and year(getdate()))
==>
datediff(yy,hdm.date_entered,getdate())<2
--------------------------------------------------------------------------------
case
case
when left(stk.product,4) between '4000' and '4999' then [stock_length]
else ''
end when '' then ''
else dtm.order_qty*
(case
when left(stk.product,4) between '4000' and '4999' then [stock_length]
else ''
end)/12
end as Footage,
==>
case
when left(stk.product,4) between '4000' and '4999' and stock_length!=''
then dtm.order_qty*[stock_length]/12
else ''
end AS Footage,
----------------------------------------------------------------------------------
(year(hdm.date_entered) between year(getdate())-1 and year(getdate()))
==>
datediff(yy,hdm.date_entered,getdate())<2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货