22,210
社区成员
发帖
与我相关
我的任务
分享sql 存储过程应用实例请大神优化查询速度现在查询7-8秒半个月的代码如下写的比较烂
USE [YCOrderManage]
GO
/****** Object: StoredProcedure [dbo].[proc_TjGetOrderNumNew] Script Date: 09/19/2017 11:33:29 ******/
/**整个存储过程功能:统计开始下单开始时间到结算各种量和总利润,根据游戏类型和平台名称筛选,主要前台传时间sql循环 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_TjGetOrderNumNew]
@beginTime datetime,--下单开始时间
@endTime datetime,--下单结束时间
@ptName varchar(200),--根据平台名称筛选
@gameName varchar(200),--游戏名称名称筛选
@fdr varchar(200),--根据发单人筛选 没用
@clr varchar(200)--根据订单处理人筛选 没用
as
declare @ddl int --订单量
declare @bdl int --爆单量
declare @cxl int --撤销量
declare @ycl int --异常量
declare @jrl int --介入量
declare @xsl int --协商量
declare @tkl int --退款量
declare @csl int --超时量
declare @mjl int --秒接量
declare @zlr1 decimal(18,2)--含结算利润
declare @zlr2 decimal(18,2)--不含结算利润
declare @zlr decimal(18,2)--总利润
declare @clsr decimal(18,2)--撤销收入
declare @clzc decimal(18,2)--撤销支出
declare @zsr decimal(18,2)--总收入
declare @zzc decimal(18,2)--总支出
declare @gameNameStr varchar(200)
declare @days int
if object_id('tempdb..#a') is not null
begin
drop table #a
end
create table #a(
Id int IDENTITY (1,1) PRIMARY KEY ,
rq varchar(100),
yxlxStr varchar(100),
dllx varchar(100),
ddl int,
bdl int,
ycl int,
cxl int,
jrl int,
xsl int,
tkl int,
csl int,
zlr decimal(18,2),
clsr decimal(18,2),
clzc decimal(18,2),
zsr decimal(18,2),
zzc decimal(18,2),
mjl int
)
-- OrderV是多张表的视图 实际上就是一张表 字段比较多分开了
SELECT @days = DATEDIFF(day, @beginTime, @endTime)
print(@days)
set @days = @days+1
DECLARE @I int
SET @I = 1
WHILE @I<= @days
BEGIN
if(@gameName ='全部' and @ptName ='全部')
begin
select @ddl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 --一天的订单量
select @bdl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('肥单', yq)<>0 --爆单量
select @cxl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('撤销', p.Ptzt)<>0
select @ycl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('异常', p.Ptzt)<>0
select @jrl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('介入', p.Ptzt)<>0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0)
select @tkl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt='退款删单'
select @zlr1= sum(sr+bdsr-zc-bdzc ) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)=0
select @zlr2 = isnull(sum( sr - bdzc ), 0) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)<>0
set @zlr = @zlr1 +@zlr2
select @clsr = sum(bdsr),@clzc = sum(bdzc),@zsr = sum(sr),@zzc = sum(zc) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单'
select @csl= COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and iscs = 1
select @mjl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and datediff( hour, jdsj,scsj )<=1 and datediff( hour, jdsj,scsj )>=0 and cfd =0 and jjd=0
end
else if(@ptName ='全部' and @gameName <>'全部')
begin
select @ddl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and yxlx=@gameName
select @bdl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('肥单', yq)<>0 and yxlx=@gameName
select @cxl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('撤销', p.Ptzt)<>0 and yxlx=@gameName
select @ycl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('异常', p.Ptzt)<>0 and yxlx=@gameName
select @jrl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('介入', p.Ptzt)<>0 and yxlx=@gameName
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) and yxlx=@gameName
select @tkl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt='退款删单' and yxlx=@gameName
select @zlr1= sum(sr+bdsr-zc-bdzc ) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)=0 and yxlx=@gameName
select @zlr2 = isnull(sum( sr - bdzc ), 0) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)<>0 and yxlx=@gameName
set @zlr = @zlr1 +@zlr2
select @clsr = sum(bdsr),@clzc = sum(bdzc),@zsr = sum(sr),@zzc = sum(zc) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and yxlx=@gameName
select @csl= COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and iscs = 1 and yxlx=@gameName
select @mjl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and datediff( hour, jdsj,scsj )<=1 and datediff( hour, jdsj,scsj )>=0 and cfd =0 and jjd=0 and yxlx=@gameName
end
else if(@ptName <>'全部' and @gameName ='全部')
begin
select @ddl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex(@ptName, yq)<>0
select @bdl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('肥单', yq)<>0 and charindex(@ptName, yq)<>0
select @cxl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('撤销', p.Ptzt)<>0 and charindex(@ptName, yq)<>0
select @ycl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('异常', p.Ptzt)<>0 and charindex(@ptName, yq)<>0
select @jrl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('介入', p.Ptzt)<>0 and charindex(@ptName, yq)<>0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) and charindex(@ptName, yq)<>0
select @tkl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt='退款删单' and charindex(@ptName, yq)<>0
select @zlr1= sum(sr+bdsr-zc-bdzc ) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)=0 and charindex(@ptName, yq)<>0
select @zlr2 = isnull(sum( sr - bdzc ), 0) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)<>0 and charindex(@ptName, yq)<>0
set @zlr = @zlr1 +@zlr2
select @clsr = sum(bdsr),@clzc = sum(bdzc),@zsr = sum(sr),@zzc = sum(zc) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex(@ptName, yq)<>0
select @csl= COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and iscs = 1 and charindex(@ptName, yq)<>0
select @mjl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and datediff( hour, jdsj,scsj )<=1 and datediff( hour, jdsj,scsj )>=0 and cfd =0 and jjd=0 and charindex(@ptName, yq)<>0
end
else if(@ptName <>'全部' and @gameName <>'全部')
begin
select @ddl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and yxlx=@gameName and charindex(@ptName, yq)<>0
select @bdl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('肥单', yq)<>0 and yxlx=@gameName and charindex(@ptName, yq)<>0
select @cxl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('撤销', p.Ptzt)<>0 and yxlx=@gameName and charindex(@ptName, yq)<>0
select @ycl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('异常', p.Ptzt)<>0 and yxlx=@gameName and charindex(@ptName, yq)<>0
select @jrl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and charindex('介入', p.Ptzt)<>0 and yxlx=@gameName and charindex(@ptName, yq)<>0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) ----协商量
select @tkl = COUNT(*) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt='退款删单' and yxlx=@gameName and charindex(@ptName, yq)<>0
select @zlr1= sum(sr+bdsr-zc-bdzc ) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)=0 and yxlx=@gameName and charindex(@ptName, yq)<>0
select @zlr2 = isnull(sum( sr - bdzc ), 0) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and charindex('结算', yq)<>0 and yxlx=@gameName and charindex(@ptName, yq)<>0
set @zlr = @zlr1 +@zlr2
select @clsr = sum(bdsr),@clzc = sum(bdzc),@zsr = sum(sr),@zzc = sum(zc) from OrderV where DATEDIFF(dd,xdsj,@beginTime) = 0 and zt<>'退款删单' and yxlx=@gameName and charindex(@ptName, yq)<>0
--这边两条跟上面一样的字数不够了
end
insert into #a (rq,yxlxStr,dllx,ddl,bdl,
ycl,cxl,jrl,tkl,csl,--,xsl
zlr,clsr,clzc,zsr,zzc,mjl)
values(
CONVERT(varchar(100), @beginTime, 23),@gameName,@ptName,@ddl,@bdl,
@ycl,@cxl,@jrl,@tkl,--,@xsl
@csl,@zlr,@clsr,@clzc,@zsr,@zzc,@mjl)--临时表统计的各个数量
set @beginTime = DATEADD(DAY,1,@beginTime)
SET @I = @I + 1
END
select * from #a
exec sp_recompile proc_TjGetOrderNumNew--每次都要重新编译不知道为什么
...全文
请发表友善的回复…
发表回复
clark_kidd 2017-09-27
- 打赏
- 举报
首先，按整个存储过程的结构来看，每次调用会进行10次左右的查询和1次insert。insert占用时间一般是很少的。
所以你应该先测试一下10个查询每个占用的时间是多久，如果每个select都要占用近1秒，就能解释这个存储过程需要执行7~8秒的原因了。然后你要考虑的是如何优化select的索引，可以使用SSMS中，“估计的执行计划”进行分析和优化。
听雨停了 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么[/quote]
我上面这样写是否有提高速度吗？[/quote]
速度提高1s 但是数据不对了，之前一天一条 现在成总计的了 [/quote]
还有一点可以优化的地方，把语句中所有的count(*)改成count(表中字段内容最短的那个字段)，然后用这个字段建立一个非聚集索引，可以大大提升速度
听雨停了 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么[/quote]
我上面这样写是否有提高速度吗？[/quote]
速度提高1s 但是数据不对了，之前一天一条 现在成总计的了 [/quote]
把最后的这一段插入代码加入到每一个if里面去
INSERT INTO #a
(
rq,
yxlxStr,
dllx,
ddl,
bdl,
ycl,
cxl,
jrl,
tkl,
csl, --,xsl
zlr,
clsr,
clzc,
zsr,
zzc,
mjl
)
VALUES
(
CONVERT(VARCHAR(100), @beginTime, 23),
@gameName,
@ptName,
@ddl,
@bdl,
@ycl,
@cxl,
@jrl,
@tkl, --,@xsl
@csl,
@zlr,
@clsr,
@clzc,
@zsr,
@zzc,
@mjl
)--临时表统计的各个数量
学无止境one 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么[/quote]
我上面这样写是否有提高速度吗？[/quote]
速度提高1s 但是数据不对了，之前一天一条 现在成总计的了
学无止境one 2017-09-20
- 打赏
- 举报
嗯 我试试
听雨停了 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么[/quote]
我上面这样写是否有提高速度吗？
学无止境one 2017-09-20
- 打赏
- 举报
嗯 我试试
OwenZeng_DBA 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么[/quote]
应该是参数嗅探的问题，还是先吧语句进行拆分下吧
学无止境one 2017-09-20
- 打赏
- 举报
这个我写的额，不重新编译，过一会儿速度奇慢无比 不知道未什么
听雨停了 2017-09-19
- 打赏
- 举报
IF (@ptName <> '全部' AND @gameName <> '全部')
BEGIN
WHILE @I <= @days
BEGIN
SELECT @ddl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @bdl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @cxl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('撤销', p.Ptzt) <> 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @ycl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('异常', p.Ptzt) <> 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @jrl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('介入', p.Ptzt) <> 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) ----协商量
SELECT @tkl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt = '退款删单'
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @zlr1 = SUM(sr + bdsr -zc -bdzc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) = 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @zlr2 = ISNULL(SUM(sr - bdzc), 0)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) <> 0
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SET @zlr = @zlr1 + @zlr2
SELECT @clsr = SUM(bdsr),
@clzc = SUM(bdzc),
@zsr = SUM(sr),
@zzc = SUM(zc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND yxlx = @gameName
AND CHARINDEX(@ptName, yq) <> 0
SELECT @csl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND iscs = 1
AND CHARINDEX(@ptName, yq) <> 0
SELECT @mjl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND DATEDIFF(hour, jdsj, scsj) <= 1
AND DATEDIFF(hour, jdsj, scsj) >= 0
AND cfd = 0
AND jjd = 0
AND CHARINDEX(@ptName, yq) <> 0
SET @beginTime = DATEADD(DAY, 1, @beginTime)
SET @I = @I + 1
END
END
INSERT INTO #a
(
rq,
yxlxStr,
dllx,
ddl,
bdl,
ycl,
cxl,
jrl,
tkl,
csl, --,xsl
zlr,
clsr,
clzc,
zsr,
zzc,
mjl
)
VALUES
(
CONVERT(VARCHAR(100), @beginTime, 23),
@gameName,
@ptName,
@ddl,
@bdl,
@ycl,
@cxl,
@jrl,
@tkl, --,@xsl
@csl,
@zlr,
@clsr,
@clzc,
@zsr,
@zzc,
@mjl
)--临时表统计的各个数量
END
SELECT *
FROM #a
EXEC sp_recompile proc_TjGetOrderNumNew--每次都要重新编
听雨停了 2017-09-19
- 打赏
- 举报
IF (@ptName = '全部' AND @gameName <> '全部')
BEGIN
WHILE @I <= @days
BEGIN
SELECT @ddl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND yxlx = @gameName
SELECT @bdl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0
AND yxlx = @gameName
SELECT @cxl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('撤销', p.Ptzt) <> 0
AND yxlx = @gameName
SELECT @ycl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('异常', p.Ptzt) <> 0
AND yxlx = @gameName
SELECT @jrl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('介入', p.Ptzt) <> 0
AND yxlx = @gameName
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) and yxlx=@gameName
SELECT @tkl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt = '退款删单'
AND yxlx = @gameName
SELECT @zlr1 = SUM(sr + bdsr -zc -bdzc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) = 0
AND yxlx = @gameName
SELECT @zlr2 = ISNULL(SUM(sr - bdzc), 0)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) <> 0
AND yxlx = @gameName
SET @zlr = @zlr1 + @zlr2
SELECT @clsr = SUM(bdsr),
@clzc = SUM(bdzc),
@zsr = SUM(sr),
@zzc = SUM(zc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND yxlx = @gameName
SELECT @csl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND iscs = 1
AND yxlx = @gameName
SELECT @mjl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND DATEDIFF(hour, jdsj, scsj) <= 1
AND DATEDIFF(hour, jdsj, scsj) >= 0
AND cfd = 0
AND jjd = 0
AND yxlx = @gameName
SET @beginTime = DATEADD(DAY, 1, @beginTime)
SET @I = @I + 1
END
END
ELSE
IF (@ptName <> '全部' AND @gameName = '全部')
BEGIN
WHILE @I <= @days
BEGIN
SELECT @ddl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX(@ptName, yq) <> 0
SELECT @bdl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0
AND CHARINDEX(@ptName, yq) <> 0
SELECT @cxl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('撤销', p.Ptzt) <> 0
AND CHARINDEX(@ptName, yq) <> 0
SELECT @ycl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('异常', p.Ptzt) <> 0
AND CHARINDEX(@ptName, yq) <> 0
SELECT @jrl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('介入', p.Ptzt) <> 0
AND CHARINDEX(@ptName, yq) <> 0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0) and charindex(@ptName, yq)<>0
SELECT @tkl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt = '退款删单'
AND CHARINDEX(@ptName, yq) <> 0
SELECT @zlr1 = SUM(sr + bdsr -zc -bdzc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) = 0
AND CHARINDEX(@ptName, yq) <> 0
SELECT @zlr2 = ISNULL(SUM(sr - bdzc), 0)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) <> 0
AND CHARINDEX(@ptName, yq) <> 0
SET @zlr = @zlr1 + @zlr2
SELECT @clsr = SUM(bdsr),
@clzc = SUM(bdzc),
@zsr = SUM(sr),
@zzc = SUM(zc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX(@ptName, yq) <> 0
SELECT @csl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND iscs = 1
AND CHARINDEX(@ptName, yq) <> 0
SELECT @mjl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND DATEDIFF(hour, jdsj, scsj) <= 1
AND DATEDIFF(hour, jdsj, scsj) >= 0
AND cfd = 0
AND jjd = 0
AND CHARINDEX(@ptName, yq) <> 0
SET @beginTime = DATEADD(DAY, 1, @beginTime)
SET @I = @I + 1
END
END
ELSE
听雨停了 2017-09-19
- 打赏
- 举报
--做的两点调整(顺便格式化了一下你的代码):
--1.存储过程里重新定义参数并赋值,避免参数嗅探，解决存储过程每次调用时都编译的问题
--2.把while循环提到了每个if里面，这样可以避免当你SET @I = @I + 1时，@I每加1都进行一次if判断。这样的话只会进一次if，然后一直在里面循环
USE [YCOrderManage]
GO
/****** Object: StoredProcedure [dbo].[proc_TjGetOrderNumNew] Script Date: 09/19/2017 11:33:29 ******/
/**整个存储过程功能:统计开始下单开始时间到结算各种量和总利润,根据游戏类型和平台名称筛选,主要前台传时间sql循环 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[proc_TjGetOrderNumNew]
@beginTime DATETIME,--下单开始时间
@endTime DATETIME,--下单结束时间
@ptName VARCHAR(200),--根据平台名称筛选
@gameName VARCHAR(200),--游戏名称名称筛选
@fdr VARCHAR(200),--根据发单人筛选 没用
@clr VARCHAR(200)--根据订单处理人筛选 没用
AS
BEGIN
--重新定义参数并赋值,避免参数嗅探
DECLARE @PbeginTime DATETIME
DECLARE @PendTime DATETIME
DECLARE @PptName VARCHAR(200)
DECLARE @PgameName VARCHAR(200)
DECLARE @Pfdr VARCHAR(200)
DECLARE @Pclr VARCHAR(200)
SET @PbeginTime = @beginTime;
SET @PendTime = @endTime;
SET @PptName = @ptName;
SET @PgameName = @gameName;
SET @Pfdr = @Pfdr;
SET @Pclr = @clr;
DECLARE @ddl INT --订单量
DECLARE @bdl INT --爆单量
DECLARE @cxl INT --撤销量
DECLARE @ycl INT --异常量
DECLARE @jrl INT --介入量
DECLARE @xsl INT --协商量
DECLARE @tkl INT --退款量
DECLARE @csl INT --超时量
DECLARE @mjl INT --秒接量
DECLARE @zlr1 DECIMAL(18, 2)--含结算利润
DECLARE @zlr2 DECIMAL(18, 2)--不含结算利润
DECLARE @zlr DECIMAL(18, 2)--总利润
DECLARE @clsr DECIMAL(18, 2)--撤销收入
DECLARE @clzc DECIMAL(18, 2)--撤销支出
DECLARE @zsr DECIMAL(18, 2)--总收入
DECLARE @zzc DECIMAL(18, 2)--总支出
DECLARE @gameNameStr VARCHAR(200)
DECLARE @days INT
IF OBJECT_ID('tempdb..#a') IS NOT NULL
BEGIN
DROP TABLE #a
END
CREATE TABLE #a
(
Id INT IDENTITY(1, 1) PRIMARY KEY,
rq VARCHAR(100),
yxlxStr VARCHAR(100),
dllx VARCHAR(100),
ddl INT,
bdl INT,
ycl INT,
cxl INT,
jrl INT,
xsl INT,
tkl INT,
csl INT,
zlr DECIMAL(18, 2),
clsr DECIMAL(18, 2),
clzc DECIMAL(18, 2),
zsr DECIMAL(18, 2),
zzc DECIMAL(18, 2),
mjl INT
)
-- OrderV是多张表的视图 实际上就是一张表 字段比较多分开了
SELECT @days = DATEDIFF(DAY, @beginTime, @endTime)
PRINT(@days)
SET @days = @days + 1
DECLARE @I INT
SET @I = 1
IF (@gameName = '全部' AND @ptName = '全部')
BEGIN
WHILE @I <= @days
BEGIN
SELECT @ddl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0 --一天的订单量
SELECT @bdl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0 --爆单量
SELECT @cxl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('撤销', p.Ptzt) <> 0
SELECT @ycl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('异常', p.Ptzt) <> 0
SELECT @jrl = COUNT(*)
FROM OrderV AS o
LEFT JOIN PtOrderInfo AS p
ON o.Id = p.OrderId
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('介入', p.Ptzt) <> 0
--select @xsl = COUNT(*) from OrderV as o left join PtOrderInfo as p on o.Id = p.OrderId where DATEDIFF(dd,xdsj,@beginTime) = 0 and (charindex('撤销', p.Ptzt)<>0 or charindex('协商', p.Ptzt)<>0)
SELECT @tkl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt = '退款删单'
SELECT @zlr1 = SUM(sr + bdsr -zc -bdzc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) = 0
SELECT @zlr2 = ISNULL(SUM(sr - bdzc), 0)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
AND CHARINDEX('结算', yq) <> 0
SET @zlr = @zlr1 + @zlr2
SELECT @clsr = SUM(bdsr),
@clzc = SUM(bdzc),
@zsr = SUM(sr),
@zzc = SUM(zc)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND zt <> '退款删单'
SELECT @csl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND iscs = 1
SELECT @mjl = COUNT(*)
FROM OrderV
WHERE DATEDIFF(dd, xdsj, @beginTime) = 0
AND DATEDIFF(hour, jdsj, scsj) <= 1
AND DATEDIFF(hour, jdsj, scsj) >= 0
AND cfd = 0
AND jjd = 0
SET @beginTime = DATEADD(DAY, 1, @beginTime)
SET @I = @I + 1
END
END
ELSE
顺势而为1 2017-09-19
- 打赏
- 举报
楼主可以先将 OrderV 与 PtOrderInfo 的连接放到临时表中, 这样就可以到临时表中取 Select count(*) , 不用每次都去Left Join
二月十六 2017-09-19
- 打赏
- 举报
太长了……
楼主中间那部分可以试试改成少一些select
SELECT @ddl = SUM(CASE WHEN DATEDIFF(dd, xdsj, @beginTime) = 0 THEN 1
ELSE 0
END) ,
@bdl = SUM(CASE WHEN DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0 THEN 1
ELSE 0
END) ,
@cxl = SUM(CASE WHEN DATEDIFF(dd, xdsj, @beginTime) = 0
AND CHARINDEX('肥单', yq) <> 0 THEN 1
ELSE 0
END)
FROM OrderV
OwenZeng_DBA 2017-09-19
- 打赏
- 举报
问题拆解下吧，确实有点太长了
繁花尽流年 2017-09-19
- 打赏
- 举报
去掉大循环，先用group by 日期去改造下看看
