27,580
社区成员
发帖
与我相关
我的任务
分享
ALTER procedure [dbo].[usp_QueryCommission]
@khid varchar(20),
@filter decimal(18,2),
@topcount int,
@begin datetime,
@end datetime
as
set nocount on;
;WITH cte AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 商户号,CAST(交易时间 as date) order by 交易时间) as rn
FROM 交易流水 where 交易时间 between @begin and @end
)
SELECT 商户号,CAST(交易时间 as date) as 交易时间,count(id) as 总单号数,sum(订单金额) as 总金额
FROM cte
WHERE rn<=@topcount AND 订单金额>=@filter and 商户号=@khid
GROUP BY 商户号,CAST(交易时间 as date)
ALTER PROCEDURE [dbo].[usp_QueryCommission]
@khid VARCHAR(20) ,
@filter DECIMAL(18, 2) ,
@topcount INT ,
@begin DATETIME ,
@end DATETIME
AS
SET NOCOUNT ON;
;
WITH cte
AS ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY 商户号,
CAST(交易时间 AS DATE) ORDER BY 交易时间 ) AS rn
FROM 交易流水
WHERE 交易时间 BETWEEN @begin AND @end
)
SELECT 商户号 ,
CAST(交易时间 AS DATE) AS 交易时间 ,
COUNT(id) AS 总单号数 ,
SUM(订单金额) AS 总金额
FROM cte
WHERE rn <= @topcount
AND 订单金额 >= @filter
AND 商户号 = @khid
GROUP BY 商户号 ,
CAST(交易时间 AS DATE)
OPTION ( RECOMPILE );
像这样
WITH cte AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 商户号,CAST(交易时间 as date) order by 交易时间) as rn
FROM 交易流水 where 交易时间 between '2011-12-1' and '2017-12-31'
)
SELECT 商户号,CAST(交易时间 as date) as 交易时间,count(id) as 总单号数,sum(订单金额) as 总金额
FROM cte
WHERE rn<=10 AND 订单金额>=50 and 商户号='2082190'
GROUP BY 商户号,CAST(交易时间 as date)
耗时0.83秒。
但是如果执行存储过程:
exec usp_QueryCommission
@khid='2082190',
@filter=10,
@topcount=50,
@begin='2011-12-1',
@end='2017-12-31'
耗时4.14秒[/quote]
可能是参数嗅探的问题,,,,,
WITH cte AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 商户号,CAST(交易时间 as date) order by 交易时间) as rn
FROM 交易流水 where 交易时间 between '2011-12-1' and '2017-12-31'
)
SELECT 商户号,CAST(交易时间 as date) as 交易时间,count(id) as 总单号数,sum(订单金额) as 总金额
FROM cte
WHERE rn<=10 AND 订单金额>=50 and 商户号='2082190'
GROUP BY 商户号,CAST(交易时间 as date)
耗时0.83秒。
但是如果执行存储过程:
exec usp_QueryCommission
@khid='2082190',
@filter=10,
@topcount=50,
@begin='2011-12-1',
@end='2017-12-31'
耗时4.14秒
SELECT *,ROW_NUMBER() OVER(PARTITION BY 商户号,CAST(交易时间 as date) order by 交易时间) as rn
FROM 交易流水 where 交易时间 between @begin and @end
AND 订单金额>=@filter and 商户号=@khid
放在内层