[骚年 ，求教SQL优化]

[雷肿么了]

其实想根据订单，自动结算库存,然后判断哪些单可以出库，标记 #TMP_TRADE.Flag 字段
可是我这样写出来，3千订单，要跑2分钟才能结算出来， 再次请高手指点下，

----下面我先贴上我的SQL 语句
-------- 定义订单列表 -------
CREATE TABLE #TMP_TRADE
(
  ID INT,
  Oid VARCHAR(50),
  UserId VARCHAR(100),
  Country VARCHAR(100),
  StateOrProvince VARCHAR(100),
  City VARCHAR(100),
  PostalCode VARCHAR(20),
  ShopID INT,
  ShippingCost DECIMAL(16,2),
  TotalPrice DECIMAL(16,2),
  SaleDate VARCHAR(20),
  PaidDate VARCHAR(20),
  ShippedDate VARCHAR(20),
  Flag INT 	 --是否可出库(有库存)   0 无库存，1有库存

)

-- 构造订单数据(实际要处理的有3千条数据)
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0
INSERT INTO #TMP_TRADE SELECT 120760,'850714117008','murrayblt','Australia','Victoria','strathmore',3041,10,0.00,1.00,'2012-07-02 00:00','2012-07-02 00:02','1900-01-01 00:00',0


------ 库存表(动态结算，暂无数据) ------
CREATE TABLE #TMP_SkuNum
(
  SKU_ID INT,
  STOCK_ID INT,
  NUM INT
)

--- SKU 产品关联表 ----
CREATE TABLE #SKU_PRODUCT_MTM
(
 ID INT,
 PRODUCT_CODE VARCHAR(20),
 SKU_CODE VARCHAR(20),
 SKU_QTY INT
)

INSERT INTO #SKU_PRODUCT_MTM SELECT 1 'SP001','SKU001',2


CREATE TABLE #Calc_Sku
(
  Id INT,	
  Num INT
)

DECLARE @T_ID INT
DECLARE @Oid VARCHAR(20)
DECLARE @Calc_Num INT
SET @T_ID=0
SET @Oid=''
DECLARE MY_CURSOR CURSOR FOR SELECT Id,Oid FROM #TMP_TRADE  
OPEN MY_CURSOR
FETCH FROM MY_CURSOR INTO @T_ID,@Oid
WHILE (@@FETCH_STATUS=0)
BEGIN
	IF EXISTS(SELECT 1 FROM tEBay_TradeProduct tetp JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE	WHERE tetp.Oid=@Oid )   --判断产品有无关联的SKU
	BEGIN			
		INSERT #Calc_Sku
		SELECT tssn.Num-tetp.Num*spm.SKU_QTY 
		FROM tEBay_TradeProduct tetp   --tEBay_TradeProduct 订单产品表，此表实际中只有20W数据 
		JOIN #SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE		
		JOIN #TMP_SkuNum tssn ON tss.Id=tssn.SKU_ID AND tssn.STOCK_ID=1
		WHERE tetp.Oid=@Oid
		
		IF EXISTS(SELECT 1 FROM #Calc_Sku WHERE Num<0)
		BEGIN		
			PRINT '无库存Oid:'+@Oid
		END
		ELSE
		BEGIN
			UPDATE #TMP_TRADE  SET Flag = 1 WHERE ID=@T_ID
			UPDATE #TMP_SkuNum SET #TMP_SkuNum.NUM = #Calc_Sku.Num FROM #TMP_SkuNum,#Calc_Sku WHERE #TMP_SkuNum.SKU_ID=#Calc_Sku.Id AND #TMP_SkuNum.STOCK_ID=1		
		END
		
		DELETE FROM #Calc_Sku
	END
	
	FETCH FROM MY_CURSOR INTO @T_ID,@Oid
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
 

SELECT * FROM #TMP_TRADE ORDER BY Flag DESC,PaidDate

DROP TABLE #TMP_TRADE
DROP TABLE #TMP_SkuNum
DROP TABLE #SKU_PRODUCT_MTM
DROP TABLE #Calc_Sku

[开着拖拉机泡妞]

为什么不用递归试试？
游标没什么效率，不到万不得已干嘛用游标

[连星入剑端]

呃，能把每个字段的作用解释的再清楚点不，包括你的思路，通过命名又猜不出来。又没主外键的，哪些表关联都弄不清，看起来比较头大呢，呵呵。
慢的主要原因是游标吧，能不用游标么？

[雷肿么了]

CREATE TABLE #TMP_TRADE
(
  ID INT,   --订单ID
  Oid VARCHAR(50), --订单编号
  UserId VARCHAR(100),
  Country VARCHAR(100),
  StateOrProvince VARCHAR(100),
  City VARCHAR(100),
  PostalCode VARCHAR(20),
  ShopID INT,
  ShippingCost DECIMAL(16,2),
  TotalPrice DECIMAL(16,2),
  SaleDate VARCHAR(20),
  PaidDate VARCHAR(20),
  ShippedDate VARCHAR(20),
  Flag INT      --是否可出库(有库存)   0 无库存，1有库存

)

------ 库存表(动态结算，暂无数据) ------
CREATE TABLE #TMP_SkuNum
(
  SKU_ID INT,   --SKU ID
  STOCK_ID INT, --仓库ID
  NUM INT       --数量
)

--- SKU 产品关联表 ----
CREATE TABLE #SKU_PRODUCT_MTM
(
 ID INT,
 PRODUCT_CODE VARCHAR(20),  --产品编码
 SKU_CODE VARCHAR(20),      --SKU编码
 SKU_QTY INT                --关联数量
)

CREATE TABLE #Calc_Sku
(
  Id INT,     --SKU ID
  Num INT     --结算库存数量
)

[雷肿么了]

PS: 一个订单商品是由多个SKU 组合而成的

CREATE TABLE tEBay_TradeProduct
(
  ID INT,                    --订单商品ID
  Oid VARCHAR(20),           --订单编号
  PRODUCT_CODE VARCHAR(20)   --	产品编码
  Num  INT                   --	数量
)
CREATE TABLE #tStock_Sku
(
  ID INT,           --SKU ID
  CODE VARCHAR(20), --sku编号
)

其实我首先是根据查询条件将订单插入订单表 #TMP_TRADE （有3千条数据）
然后关联tEBay_TradeProduct （订单产品表，有20W数据）根据各自的Oid关联，
然后再根据tEBay_TradeProduct的PRODUCT_CODE（产品编号）与#SKU_PRODUCT_MTM（订单SKU关联表） 的PRODUCT_CODE关联
然后#SKU_PRODUCT_MTM（订单SKU关联表）的SKU_CODE 与#tStock_Sku（SKU 表）表的SKU_CODE关联，
最后#tStock_Sku（SKU 表）表 与#TMP_SkuNum（库存表）的SKU_ID
关联

就这样，每个订单找到关联的SKU，并结算库存，最后得出能否出库

[雷肿么了]

--之前写的时候漏了，红色部分，没敲上来
		INSERT #Calc_Sku
		SELECT tss.id,tssn.Num-tetp.Num*spm.SKU_QTY 
		FROM tEBay_TradeProduct tetp
		JOIN SKU_PRODUCT_MTM spm ON tetp.SKU=spm.PRODUCT_CODE
		JOIN tStock_Sku tss ON tss.Code=spm.SKU_CODE
		JOIN #TMP_SkuNum tssn ON tss.Id=tssn.SKU_ID AND tssn.STOCK_ID=@StockID
		WHERE tetp.Oid=@Oid

[筱筱澄]

没看的懂为什么要用游标，直接更新会是情况？
另外没有特殊需要的话，尽量使用FORWARD_ONLY READ_ONLY STATIC游标；

[Rotel-刘志东]

游标的效率是很差的，不到万不得已的时候不要用游标。
可以用存储过程实现代替游标的功能。

[雷肿么了]

最后没用游标写了， 用游标确实慢了很多，

我直接查出来，递减库存就可以了。 2秒搞定。 感觉还可以优化的。