sql执行的慢该怎么去优化?

数据有120w,查数据要差不多17分钟,要怎么去优化它啊,求大佬帮忙!

SELECT   '餐饮' as 渠道,TBStoreChannelDMS.ChannelName as 标准渠道,(select name from  middleData.dbo.SystemUserDepartmentSteve  where id=
(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve where ID=(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve
where ID=(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve where ID=(select Top 1 FK_SystemUserDepartment_Create_ID 
from TBStoreDMS where id=c.id))))) as 大区,
(select Name from middleData.dbo.SystemUserDepartmentSteve where ID=(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve
where ID=(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve where ID=(select Top 1 FK_SystemUserDepartment_Create_ID 
from TBStoreDMS where id=c.id)))) as 战区,
(select Name from middleData.dbo.SystemUserDepartmentSteve where ID=(select FK_SystemUserDepartment_ID from middleData.dbo.SystemUserDepartmentSteve
where ID=(select Top 1 FK_SystemUserDepartment_Create_ID from TBStoreDMS where id=c.ID))) as 营办,
(select [Name] from TBProvince where ID=(select Top 1 FK_TBProvince_ID from Dealer where Code=a.DealerCode)) as 省份,
(select [Name] from TBCity where ID=(select Top 1 FK_TBCity_ID from Dealer where Code=a.DealerCode)) as 城市,[a].DealerName AS 经销商名称, [a].DealerCode AS 经销商代码, 
convert(varchar(10),[a].OrderDate,20) AS 订单日期,Year(OrderDate)*100+Month(OrderDate) as 单据月份,
[a].OrderNumber AS 订单号码, [a].RetailerCode AS 经销商门店代码, [a].RetailerName AS 经销商门店名称,
a.Address as 经销商门店地址,dds.DMSCode as DMS门店编码,[c].Name AS 标准门店名称, [c].Code AS 标准门店代码,
std.Name as 标准门店类型, cg.[Code] 标准门店客户分组代码,cg.Name 标准门店客户分组名称,[c].StreetAddress AS 标准门店地址,  [a].ProductCode AS 经销商产品代码, 
[a].ProductName AS 经销商产品名称,[a].Num as 原始数量,[j].ProductUnit AS 原始单位,[a].Price 原始单价, 
[a].Num*(case when j.ConversionNum is null then 1 else j.ConversionNum end) AS 转化后数量,
case when [a].ProductName like '%哈根%' or [a].ProductName like '%冰淇淋%' then '杯'
when  [a].ProductName like '%湾仔%' or [a].ProductName like '%水饺%' then '袋' else '包' end 转化后单位,
[a].Price/(case when j.ConversionNum is null then 1 else j.ConversionNum end) AS 转化后单价, 
[a].Amount AS 经销商总价,[b].Code AS 标准产品代码, [b].ProductName AS 标准产品名称,
[e].Name AS 标准产品品牌, [b].PackageSize AS 标准产品规格,f.MingChen as 标准产品统计单位,h.Name as 产品最小可售单位,
(a.Num*(case when j.ConversionNum is null then 1 else j.ConversionNum end))/case when b.PackageSize='0' then 1 when ISNUMERIC(b.PackageSize)=0 then 1 else casT( b.PackageSize as decimal(18,0)) end  as 转换箱数,
g.UnitPrice as [标准单价(含税)],g.UntaxedPrice as [标准单价(未税)],g.UnitPrice*a.Num*(case when j.ConversionNum is null then 1 else j.ConversionNum end) as [标准总价(含税)],
g.UntaxedPrice*a.Num*(case when j.ConversionNum is null then 1 else j.ConversionNum end) as [标准总价(未税)]
,i.ConfirmType as '门店匹配',j.ConfirmType as '产品匹配'
FROM  DDI_OriginalSalesData AS [a] WITH(NOLOCK)
left join DDI_DealerRetailerRelation i 
on ltrim(rtrim(a.RetailerName))=ltrim(rtrim(i.OriginalRetailerName)) and a.DealerCode=i.DealerCode and 
a.DealerName=i.DealerName and ltrim(rtrim(a.RetailerCode))=ltrim(rtrim(i.OriginalRetailerCode)) and i.IsValid=1 and i.IsDelete=0
left join DMSStoreDetail ddd on ddd.FK_DDIDealerRelation_ID=i.ID and ddd.IsValid=1 and ddd.IsDelete=0
left join DMSStore dds on dds.ID=ddd.FK_DMSStore_ID and dds.IsValid=1 and dds.IsDelete=0
left join DDI_ProductMatching j on
a.ProductName=j.DealerProductName and a.ProductCode=j.DealerProductCode and 
a.DealerCode=j.DealerCode and a.DealerName=j.DealerName and j.IsDelete=0 and j.IsValid=1
Left Join dbo.TBProductInformationDMS AS [b] WITH(NOLOCK) ON j.FK_TBProductInformation=b.ID 
Left Join dbo.TBProductBrandDMS AS [e] WITH(NOLOCK) ON e.ID=b.FK_TBProductBrand_ID
Left Join dbo.TBProductSeriesDMS AS [d] WITH(NOLOCK) ON d.ID=b.FK_TBProductSeries_ID 
Left Join dbo.TBStoreDMS AS [c] WITH(NOLOCK) ON dds.FK_MDMStore_ID=c.ID 
left join dbo.TBStoreChannelDMS  WITH(NOLOCK) ON TBStoreChannelDMS.ID= c.FK_TBStoreChannel_ID
left join TBChanPinTongJiDanWeiDMS f on b.FK_TBChanPinTongJiDanWei_ID=f.ID
left join TBMinimumSaleableUnitDMS h on b.FK_TBMinimumSaleableUnit_ID=h.ID
left join TBProductPrice g on g.FK_Product_ID=b.ID and g.FK_StoreChanne_ID= '95D5C467-B4E6-4F91-9AB3-1DD1DF9A9A7A'
left join TBStoreTypeDMS std on std.ID=c.FK_TBStoreType_ID
left join TBStoreCustomerGroupManagement cg on cg.ID=c.FK_TBStoreCustomerGroupM_ID and cg.IsValid=1 and cg.IsDelete=0
WHERE a.IsDelete=0 and a.IsValid=1 

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

haunting_1

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值