1. 问题描述，分析
在sql management studio 执行此sql 需2分钟，优化器提示需建索引
SQLQuery65.sql - erpdb.zhcxkj.com,1433.irobotbox (irobotboxuser (1629)) 中缺少索引的详细信息
查询处理器估计采用以下索引可以将查询开销减少 65.5746%。
*/
/*
USE [tempdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[#temp_events________________________________________________________________________________________________________0000000EC146] ([ClientOrderCode])
GO
*/
CREATE NONCLUSTERED INDEX [IX_RB_Amazon_ListFinancialEvents_CustomerID_FinancialEventType_AmazonOrderId_PostedDate]
ON dbo.RB_Amazon_ListFinancialEvents (CustomerID, FinancialEventType, AmazonOrderId, PostedDate)
INCLUDE (OrderSourceID)
2.解决方案建议：
2.1根据提示：创建索引后 28秒执行完成。
2.2 此类问题建议开发的同事，对自己写的sql 查询效率做到心中有数，执行效果不好，要根据SQL优化器提示，让DBA帮助创建索引，拿不定主意的请DBA给出建议，到达比较好的效果后在上线。
3. 原查询SQL
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id(N'tempdb..#temp_orderinfo')
AND type = 'U')
BEGIN
DROP TABLE #temp_orderinfo
END
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id(N'tempdb..#temp_events')
AND type = 'U')
BEGIN
DROP TABLE #temp_events
END
SELECT A.ClientOrderCode,
A.OrderSourceID
INTO #temp_orderinfo
FROM RB_OrderInfo A(NOLOCK)
INNER JOIN dbo.RB_OrderSource B(NOLOCK)
ON A.OrderSourceID = B.ID
WHERE A.CustomerID = 1
AND A.IsStockOrder = 0
AND A.IsFBAOrder = 1
AND A.ShipTime >='2022/4/6 0:00:00'
AND A.ShipTime <= '2022/5/6 0:00:00'
AND B.OrderSourceType = 1
AND A.PromotionType LIKE '%MarketplaceFacilitator%'
GROUP BY A.ClientOrderCode,
A.OrderSourceID
SELECT B.AmazonOrderId AS ClientOrderCode
INTO #temp_events
FROM #temp_orderinfo A
INNER JOIN dbo.RB_Amazon_ListFinancialEvents B(NOLOCK)
ON A.ClientOrderCode = B.AmazonOrderId
AND A.OrderSourceID = B.OrderSourceID
WHERE B.CustomerID = 1
AND B.PostedDate >= '2022/4/6 0:00:00'
AND Isnull(B.AmazonOrderId, '') <> ''
AND B.FinancialEventType = 'ShipmentEvents'
GROUP BY B.AmazonOrderId
SELECT Count(DISTINCT ClientOrderCode)
FROM #temp_orderinfo
SELECT Count(DISTINCT ClientOrderCode)
FROM #temp_events
SELECT TOP 10 A.ClientOrderCode
FROM #temp_orderinfo A
LEFT JOIN #temp_events B
ON A.ClientOrderCode = B.ClientOrderCode
WHERE B.ClientOrderCode IS NULL