急急急!改一下SQL语句的子句!必须是改完整点!可得到95%的分

xzwms2010 2012-07-03 04:15:41

-----比较卡的4个查询一下4个 请大家看看什么问题 造成的
----- 5832条数据 本地一共 要用28秒 服务器上要60秒以上 太慢了
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量


---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量

from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId


要求改成把中间4个select 子句改成 把子查询表作为一个连接,那只要扫一次就行了



参考帖子:http://topic.csdn.net/u/20120703/11/e278f81f-58ce-4059-bba0-20b50b2e21fa.html
...全文
203 20 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 18 楼 的回复:]
SQL code


select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and St……
[/Quote]
辛苦了!
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 17 楼 的回复:]
SQL code

select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName ,
t6.OldQty,t7.NewQty,PeriodIn,PeriodOut
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 o……
[/Quote]
辛苦您了!感谢你!技术不错!表扬一下!
  • 打赏
  • 举报
回复

select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量


---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量

from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId


--2005以上版本
;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1

;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m


select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId

select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId


select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId

--2000


select * into #Wms_StockLogA
from Wms_StockLog a
where id=(select MIN(id) from Wms_StockLog b
where a.SkuId=b.SkuId and a.StockId=b.StockId )


select * into #Wms_StockLogb
from Wms_StockLog a
where id=(select MAX(NewQty) from Wms_StockLog b
where a.SkuId=b.SkuId and a.StockId=b.StockId)


select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId

select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId


select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId

--有语法问题自己调试一下,我这没表结构没法调试
筱筱澄 2012-07-03
  • 打赏
  • 举报
回复
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName ,  
t6.OldQty,t7.NewQty,PeriodIn,PeriodOut
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
LEFT JOIN
select skuId,StockId,
sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,
sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,
from dbo.Wms_StockLog
GROUP BY skuId,StockId
) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId
LEFT JOIN
(
select SkuId, StockId,OldQty from dbo.Wms_StockLog where id=(SELECT min(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)
) AS t6 ON t0.skuId=t6.SkuId and t0.StockId=t6.StockId
LEFT JOIN
(
select SkuId, StockId,NewQty from dbo.Wms_StockLog where id=(SELECT max(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)
) AS t7 ON t0.skuId=t7.SkuId and t0.StockId=t7.StockId

--试试这个结果对不对
--如果正确,这已是我能尽到的最大能力了
--另外的优化就是索引了
--1、确保你的所有表都有主键
--2、Prod_Sku 表的skuId StockId 有复合索引
--3、Wms_StockLog 表的skuId StockId 有复合索引
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 15 楼 的回复:]
SQL code


select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and St……

[/Quote]


消息 102,级别 15,状态 1,第 44 行
'AddedQty' 附近有语法错误。
消息 102,级别 15,状态 1,第 50 行
'AddedQty' 附近有语法错误。

好像应为“select 或者(“
  • 打赏
  • 举报
回复

select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量


---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量

from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId


;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1

;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m


select skuId,StockId
sum(AddedQty) into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId

select skuId,StockId
sum(AddedQty) into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId


select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId

--try

xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 的回复:]
引用 12 楼 的回复:

引用 7 楼 的回复:
SQL code


--执行这个语句对比下面的,首先看结果一致不,然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache ('ALL','default');

--[语句执行花费时间(毫秒)]
decl……
[/Quote]

大哥谢谢你呀!有点希望了!嘻嘻
筱筱澄 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 的回复:]

引用 7 楼 的回复:
SQL code


--执行这个语句对比下面的,首先看结果一致不,然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache ('ALL','default');

--[语句执行花费时间(毫秒)]
declare @d datetim……
[/Quote]
为什么在一起执行呢,
对了就说明 sum 那一块可以替换掉

现在就剩下 top 那一块了。
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 的回复:]
SQL code


--执行这个语句对比下面的,首先看结果一致不,然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache ('ALL','default');

--[语句执行花费时间(毫秒)]
declare @d datetime
set @d……
[/Quote]

我用A本地测试库 5832条 使用
第一个用了4秒 改成了 left join 对

第二个用了7秒

一起执行 是 12秒

你写的那个测试秒 是0秒

我是看的Sqlserver上的显示12秒

B服务器正式库--测试 7963条

第一个 花了9秒

第二个花了 12秒!

如果一起 查询 是24秒


  • 打赏
  • 举报
回复

select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量


---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量

from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId


;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1

;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m


select skuId,StockId
sum(AddedQty) into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId

select skuId,StockId
sum(AddedQty) into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId


select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId

--try
lzly0812 2012-07-03
  • 打赏
  • 举报
回复
怎么可以看到它运行多久了啊!!!
不如给这些放到存储过程里面去撒,听说那里面比较快。
筱筱澄 2012-07-03
  • 打赏
  • 举报
回复
你这是个查询吗?
建议

,(select top 1 OldQty from dbo.Wms_StockLog where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量

这个库存量的能查出来放入到临时表里面

让你这个语句分布来操作。
连星入剑端 2012-07-03
  • 打赏
  • 举报
回复
如果确实想解决问题的话,把你的测试数据准备好,
提供表结构的详细说明,包括字段说明,每张表的主键、外键、索引情况,表的数据量大小,要达到的效果,等等,这样大家才好帮你。
只是给条sql语句,只能就语法层面分析,其它的一些因素是看不到的。
同时可以去看一下执行计划,那个是调优必看的东西。
筱筱澄 2012-07-03
  • 打赏
  • 举报
回复

--执行这个语句对比下面的,首先看结果一致不,然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache ('ALL','default');

--[语句执行花费时间(毫秒)]
declare @d datetime
set @d=getdate()
/*你的sql脚本开始*/

/*你的sql脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())


*/
--1
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
PeriodIn,PeriodOut
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
INNER JOIN /*如果结果不对,把这里改成left join 试试 */(
select skuId,StockId,
sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,
sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,
from dbo.Wms_StockLog
GROUP BY skuId,StockId
) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId


-------------------------------------------------------------
--2
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName

---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量

from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 的回复:]
上一个结贴了啊,看在没帮到你,又给我分的情况下,我给你好好看看
[/Quote]
好的!如果解决!等以后有分在给多留些分!
筱筱澄 2012-07-03
  • 打赏
  • 举报
回复
上一个结贴了啊,看在没帮到你,又给我分的情况下,我给你好好看看
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 的回复:]

引用 1 楼 的回复:
又开新帖?真有米,给了份不能解决问题,有点浪费。

建议你把查询分步走来处理,把没一个复杂的查询数据单独放到临时表,
然后最后对临时表进行简单的查询处理


大哥我分都干了 最后79分 了! 那100分没有!他们给的建议 我改了5种写法!可能是我写的有问题!查询出来的数据10万多条和 8万多条!好多冗余数据!正确的是5832条!我的心都凉了!

目前只……
[/Quote]

你误会我的意思了,我是说问题没解决就不要结贴,继续拿回那个帖子就好
shoppo0505 2012-07-03
  • 打赏
  • 举报
回复
各个表格连接的列都设为主键和index了没?
这点数据量就卡,肯定是表格设计不合理。
xzwms2010 2012-07-03
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 的回复:]
又开新帖?真有米,给了份不能解决问题,有点浪费。

建议你把查询分步走来处理,把没一个复杂的查询数据单独放到临时表,
然后最后对临时表进行简单的查询处理
[/Quote]

大哥我分都干了 最后79分 了! 那100分没有!他们给的建议 我改了5种写法!可能是我写的有问题!查询出来的数据10万多条和 8万多条!好多冗余数据!正确的是5832条!我的心都凉了!

目前只求解决!不求回复!宁愿你回复少或不回复! 求实力派解决实际问题!
  • 打赏
  • 举报
回复
又开新帖?真有米,给了份不能解决问题,有点浪费。

建议你把查询分步走来处理,把没一个复杂的查询数据单独放到临时表,
然后最后对临时表进行简单的查询处理

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧