select distinct mo.MoCode,mod.SortSeq,mod.InvCode,inv.cInvName,mod.Qty,dbo.fn_SumClum(fmbd.MoDId,2) as SMT,dbo.fn_SumClum(fmbd.MoDId,3) as DIP,dbo.fn_SumClum(fmbd.MoDId,4) as ASSY,dbo.fn_SumClum(fmbd.MoDId,5) as TEST,dbo.fn_SumClum(fmbd.MoDId,6) as PACK,
convert(varchar(100),(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 第一报工时间,
convert(varchar(100),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 最后报工时间,
case when ISNULL(cast(dbo.fn_SumClum(fmbd.MoDId,2) as nvarchar),N'')!=N'' then (case when cast(mod.Qty as float)-cast(dbo.fn_SumClum(fmbd.MoDId,3) as float)=0 then '是' else '否' end) else (case when cast(mod.Qty as float)-cast(dbo.fn_SumClum(fmbd.MoDId,6) as float)=0 then '是' else '否' end) end as 是否结案 ,
(DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7 as 超期时间,
case when (DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7>0 then '已' else '未' end as 是否超期 from fc_MoRoutingBill fmb
inner join fc_MoRoutingBilldetail fmbd on fmb.Mid=fmbd.MID
inner join mom_order mo on fmbd.MoId=mo.MoId
inner join mom_orderdetail mod on fmbd.MoDId=mod.MoDId
inner join mom_morder mm on fmbd.ModId=mm.MoDId
inner join inventory inv on mod.InvCode=inv.cInvCode
我这个sql查询出来慢死了,请求大虾优化方法 求代码SQL
convert(varchar(100),(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 第一报工时间,
convert(varchar(100),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 最后报工时间,
case when ISNULL(cast(dbo.fn_SumClum(fmbd.MoDId,2) as nvarchar),N'')!=N'' then (case when cast(mod.Qty as float)-cast(dbo.fn_SumClum(fmbd.MoDId,3) as float)=0 then '是' else '否' end) else (case when cast(mod.Qty as float)-cast(dbo.fn_SumClum(fmbd.MoDId,6) as float)=0 then '是' else '否' end) end as 是否结案 ,
(DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7 as 超期时间,
case when (DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7>0 then '已' else '未' end as 是否超期 from fc_MoRoutingBill fmb
inner join fc_MoRoutingBilldetail fmbd on fmb.Mid=fmbd.MID
inner join mom_order mo on fmbd.MoId=mo.MoId
inner join mom_orderdetail mod on fmbd.MoDId=mod.MoDId
inner join mom_morder mm on fmbd.ModId=mm.MoDId
inner join inventory inv on mod.InvCode=inv.cInvCode
我这个sql查询出来慢死了,请求大虾优化方法 求代码SQL
几个表,没实际情况和分析,没法说个一二三
select @SumClum=cast(cast(sum(QualifiedQty) as float) as decimal(12,2))
from fc_MoRoutingBilldetail a
inner join fc_MoRoutingBill b on a.MID=b.MID
where a.MoDId=@Modid and b.WcId=@FC
mo.MoCode ,
mod.SortSeq ,
mod.InvCode ,
inv.cInvName ,
mod.Qty ,
dbo.fn_SumClum(fmbd.MoDId, 2) AS SMT ,
dbo.fn_SumClum(fmbd.MoDId, 3) AS DIP ,
dbo.fn_SumClum(fmbd.MoDId, 4) AS ASSY ,
dbo.fn_SumClum(fmbd.MoDId, 5) AS TEST ,
dbo.fn_SumClum(fmbd.MoDId, 6) AS PACK ,
CONVERT(VARCHAR(100), ( SELECT MIN(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID --
WHERE b1.MoId = fmbd.MoId
), 120) AS 第一报工时间 ,
CONVERT(VARCHAR(100), ( SELECT MAX(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID ---
WHERE b1.MoId = fmbd.MoId
), 120) AS 最后报工时间 ,
CASE WHEN ISNULL(CAST(dbo.fn_SumClum(fmbd.MoDId, 2) AS NVARCHAR), N'') != N''
THEN ( CASE WHEN CAST(mod.Qty AS FLOAT)
- CAST(dbo.fn_SumClum(fmbd.MoDId, 3) AS FLOAT) = 0
THEN '是'
ELSE '否'
END )
ELSE ( CASE WHEN CAST(mod.Qty AS FLOAT)
- CAST(dbo.fn_SumClum(fmbd.MoDId, 6) AS FLOAT) = 0
THEN '是'
ELSE '否'
END )
END AS 是否结案 ,
( DATEDIFF(day,
( SELECT MIN(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID ---
WHERE b1.MoId = fmbd.MoId
),
( SELECT MAX(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID ---
WHERE b1.MoId = fmbd.MoId
)) ) - 7 AS 超期时间 ,
CASE WHEN ( DATEDIFF(day,
( SELECT MIN(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID ---
WHERE b1.MoId = fmbd.MoId
),
( SELECT MAX(CreateTime)
FROM fc_MoRoutingBill a1
INNER JOIN fc_MoRoutingBilldetail b1 ON a1.MID = b1.MID ---- 子查询太多,写在链接里面如何?
WHERE b1.MoId = fmbd.MoId
)) ) - 7 > 0 THEN '已'
ELSE '未'
END AS 是否超期
FROM fc_MoRoutingBill fmb
INNER JOIN fc_MoRoutingBilldetail fmbd ON fmb.Mid = fmbd.MID
INNER JOIN mom_order mo ON fmbd.MoId = mo.MoId
INNER JOIN mom_orderdetail mod ON fmbd.MoDId = mod.MoDId
INNER JOIN mom_morder mm ON fmbd.ModId = mm.MoDId
INNER JOIN inventory inv ON mod.InvCode = inv.cInvCode
先在查询分析器的预估执行计划执行一篇,SQL引擎会提示是否缺失关键索引,
例如in 相关联的的filed ,和WHERE后面的条件列 hr_no和state可以建立组合,索引
Sign_Man可建索引,索引建立原则就是遵循:where条件列、主外键列均需要建立index
这是优化的基础和core,没有恰当索引,意味着巨大cost2. 找瓶颈:
1)看懂执行计划,关注cost大的操作,如果cost大于1通常有很大的优化空间,占比大
的,cost大于1的都是优化的重点或为查询的瓶颈操作
2)关注是cpu 还是I/O开销大 ,如果I/O开销大,可以使用cpu换I/O的方法基于此,楼主的SQL优化过程也就出来了:
1、确认连接字段是否已经创建索引了,如果无,先创建index
2、大表关联可以先取按条件建立临时表数据,然后按临时表数据和其他表关联
3、优化的量化开关,关注执行计划的cost,打开io, time,profile on
如set statistics io on
set statistics time on
set statistics profile on
然后每次修改进行对比。。最好选择最优的SQL..
4. 自定义函数遵循同样原则