我在根据我将参数换成实数值的基础上弄清楚为什么查询要花费更长的时间才能运行时遇到了问题。
DECLARE @quarter int
DECLARE @year int
DECLARE @countOfUnitsBought int
set @year = 2009
set @quarter = 1
set @countOfUnitsBought = 4;
with res
as
(
select
o.account_id
--,orderyear
--,orderquarter
from
fmtables.[dbo].[orders] o
--cross apply(values(year(o.[ship_date]))) as a1(orderyear)
--cross apply(values(DatePart(quarter,(o.[ship_date])))) as a2(orderquarter)
where
ship_date = (select min(ship_date) from fmtables.[dbo].[orders] mo where [account_id] = o.account_id) and
total_value > 0 AND
order_status NOT LIKE 'return%' AND
order_status NOT LIKE 'cancel%' AND
order_status NOT LIKE 'freeze%' and
CAST(DatePart(quarter,(o.[ship_date])) as int) = @quarter and
year(o.[ship_date]) = @year and
(select sum(quantity) from fmtables..[orders] ox inner join fmtables..[orderlines] olx on ox.order_id = olx.order_id
where olx.order_id = o.order_id and [product_code] in(select [product_code] from fmtables..[products] where [category_code] in('1','2','3','4'))) >= @countOfUnitsBought
)
select * from res;
该查询需要43秒才能运行。
现在,如果我只是替换@quarter并更改为文字
CAST(DatePart(quarter,(o.[ship_date])) as int) = 1 and
现在需要1秒。
任何人都可以给我一个线索,说明为什么以及是否需要更改一些帮助。谢谢斯科特
编辑:
因此,在每个人的评论的帮助下,我设法让它变得扑朔迷离。我混合使用了从输入传递参数,然后传递给过程内部的“局部”变量的方法。
alter procedure [dbo].[Lifetime_HeadsetUnits]
@inquarter int , @inyear int, @incountOfUnitsBought int
as
DECLARE @quarter int
DECLARE @year int
declare @countOfUnitsBought int
select @quarter = @inquarter
select @year = @inyear
select @countOfUnitsBought = @incountOfUnitsBought
以及
作为最终输出查询一部分的OPTION(OPTIMIZE FOR(@quarter = 1))。
试试这个吧。我重写了datepart,以便可以使用索引,并且数据库不会在所有行上进行冗长的计算。换句话说,我使您的日期计算可修改:
DECLARE @quarter int
DECLARE @year int
DECLARE @countOfUnitsBought int
set @year = 2009
set @quarter = 1
declare @from datetime = dateadd(quarter, @quarter - 1, cast(@year as char(4)))
set @countOfUnitsBought = 4;
with res
as
(
select
o.account_id
from
fmtables.[dbo].[orders] o
where
ship_date =
(select min(ship_date)
from fmtables.[dbo].[orders] mo
where [account_id] = o.account_id) and
total_value > 0 AND
order_status NOT LIKE 'return%' AND
order_status NOT LIKE 'cancel%' AND
order_status NOT LIKE 'freeze%' and
o.[ship_date] >= @quarter and
o.[ship_date] < DATEADD(QUARTER, 1, @from) and
(select sum(quantity) from fmtables..[orders] ox
inner join fmtables..[orderlines] olx on ox.order_id = olx.order_id
where [product_code] in(select [product_code] from fmtables..[products]
where [category_code] in('1','2','3','4'))) >= @countOfUnitsBought
)
select * from res;
您正在运行sql-server 2008吗?有一个错误也可以解释您的性能问题。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句