1.问题描述分析
多列，函数运算查询，非常慢，2分钟查询
2. 解决方案 :
分析执行计划，创建索引后，6秒执行完成
CREATE NONCLUSTERED INDEX [IX_RB_Report_OrderSettlement_CustomerID_PayDate_Profits_SalesPrice]
ON [dbo].[RB_Report_OrderSettlement] ([CustomerID],[PayDate])
INCLUDE ([Profits],[SalesPrice],[TransportPay],[SalesRefunds],[PromotionRebates],[OtherCredits],[Tax],[MarketplaceFacilitatorTax])
GO
3.sql:
select
isnull(tmpfee.Profits,0) as OperateFee--毛利润
,isnull(tmpfee.SalesRefunds,0) as RefundPrice--退款金额
,isnull(tmpfee.SalesPrice,0) as SalesPrice--销售收入
,isnull(tmpfee.TransportPay,0) as TransportPay--运费收入
,isnull(tmpfee.TaxIncome,0) AS TaxIncome--税费收入
,isnull(tmpfee.OtherCredits,0) as OtherIncomeAmount--其他收入
,isnull(tmpfee.PromotionRebates,0) as PromotionFee--折扣费
,0 as RefundOrderNum
,tmpfee.ReportDay as ReportDay
from
(
select
CONVERT(varchar(10),s.PayDate,120) as ReportDay,
SUM(s.Profits) Profits,
SUM(s.SalesPrice) SalesPrice,
SUM(s.TransportPay) TransportPay,
SUM(s.SalesRefunds) SalesRefunds,
SUM(s.PromotionRebates) PromotionRebates,
SUM(s.OtherCredits) OtherCredits,SUM((ISNULL(s.Tax,0)+ISNULL(s.MarketplaceFacilitatorTax,0))) as TaxIncome
FROM RB_Report_OrderSettlement s WITH (NOLOCK)
WHERE s.CustomerID =1 AND s.PayDate BETWEEN '2020-01-01' AND '2022-01-01'
GROUP BY CONVERT(varchar(10),s.PayDate,120)
) tmpfee
根据解决方案 已完成创建索引，查询6秒完成