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

[xzwms2010]

-----比较卡的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

[开着拖拉机泡妞]

又开新帖？真有米，给了份不能解决问题，有点浪费。

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

[xzwms2010]

[Quote=引用 1 楼 的回复:]
又开新帖？真有米，给了份不能解决问题，有点浪费。

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

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

目前只求解决！不求回复！宁愿你回复少或不回复！ 求实力派解决实际问题！

[shoppo0505]

各个表格连接的列都设为主键和index了没？
这点数据量就卡，肯定是表格设计不合理。

[开着拖拉机泡妞]

[Quote=引用 2 楼 的回复:]

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

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


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

目前只……
[/Quote]

你误会我的意思了，我是说问题没解决就不要结贴，继续拿回那个帖子就好

[筱筱澄]

上一个结贴了啊，看在没帮到你，又给我分的情况下，我给你好好看看

[xzwms2010]

[Quote=引用 5 楼 的回复:]
上一个结贴了啊，看在没帮到你，又给我分的情况下，我给你好好看看
[/Quote]
好的！如果解决！等以后有分在给多留些分！

[筱筱澄]

--执行这个语句对比下面的，首先看结果一致不，然后看速度有没有变快
--记得清空缓存
/*
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

[连星入剑端]

如果确实想解决问题的话，把你的测试数据准备好，
提供表结构的详细说明，包括字段说明，每张表的主键、外键、索引情况，表的数据量大小，要达到的效果，等等，这样大家才好帮你。
只是给条sql语句，只能就语法层面分析，其它的一些因素是看不到的。
同时可以去看一下执行计划，那个是调优必看的东西。

[筱筱澄]

你这是个查询吗？
建议

,(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--库存新数量

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

让你这个语句分布来操作。

[lzly0812]

怎么可以看到它运行多久了啊！！！
不如给这些放到存储过程里面去撒，听说那里面比较快。

[开着拖拉机泡妞]

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]

[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秒

[筱筱澄]

[Quote=引用 12 楼 的回复:]

引用 7 楼 的回复:
SQL code


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

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

现在就剩下 top 那一块了。

[xzwms2010]

[Quote=引用 13 楼 的回复:]
引用 12 楼 的回复:

引用 7 楼 的回复:
SQL code


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

--[语句执行花费时间(毫秒)]
decl……
[/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
  

;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]

[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 ,  
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 有复合索引

[开着拖拉机泡妞]

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

--有语法问题自己调试一下，我这没表结构没法调试

[xzwms2010]

[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]
辛苦您了！感谢你！技术不错！表扬一下！

[xzwms2010]

[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]
辛苦了！