sqlserver 订单迁移的优化

场景 每天 数十万的的订单的迁移 随着基表的不断扩大 导致迁移越来越慢作了以下更改

 方案利用 sqlserver 函数@@rowcount 返回印象的函数 代替select count 返回的数量 优化 全表查询的次数哦

修改前的sql

DECLARE @count INT
SELECT @count = count(0) FROM  T_ORDER_TEMP h WHERE EXISTS (SELECT 1  FROM ##temp_order_history_insert WHERE id=h.ID)
and NOT EXISTS(SELECT 1 FROM T_ORDER ho WHERE ho.id=h.id)
while(0<@count)
BEGIN
Insert Into ##temp_order_history_insert_loop 
 SELECT top  5000  *
 FROM T_ORDER_TEMP h   WITH(NOLOCK)   WHERE EXISTS (SELECT 1  FROM ##temp_order_history_insert t WHERE t.id=h.ID) and NOT EXISTS(SELECT 1 FROM T_ORDER ho  WITH(NOLOCK)  WHERE ho.id=h.id) 
;
-- 创建分页的表
 BEGIN TRAN
update h set h.HISTORY_STATUS='1' FROM T_ORDER_TEMP h with(nolock) WHERE EXISTS (SELECT 1 from ##temp_order_history_insert_loop ih where h.id=ih.id);
INSERT into T_ORDER
SELECT  *
FROM ##temp_order_history_insert_loop;

COMMIT tran;
TRUNCATE TABLE ##temp_order_history_insert_loop;
SELECT @count = count(0) FROM  T_ORDER_TEMP h WHERE EXISTS (SELECT 1  FROM ##temp_order_history_insert WHERE id=h.ID)
and NOT EXISTS(SELECT 1 FROM T_ORDER ho WHERE ho.id=h.id)
END;

修改后的sql

while(1=1)
 BEGIN
 SELECT * Into #temp_order_history_insert_loop
  FROM (SELECT top  5000 *
  FROM T_ORDER_TEMP h   WITH(NOLOCK)   WHERE EXISTS (SELECT 1  FROM #temp_order_history_insert t WHERE t.id=h.ID) and NOT EXISTS(SELECT 1 FROM T_ORDER ho  WITH(NOLOCK)  WHERE ho.id=h.id))T;
 IF(@@ROWCOUNT<=0)
 BEGIN
  BREAK;
 END 
 -- 创建分页的表
 BEGIN TRAN
  update h set h.HISTORY_STATUS='1' FROM T_ORDER_TEMP h with(nolock) WHERE EXISTS (SELECT 1 from #temp_order_history_insert_loop ih where h.id=ih.id);
  INSERT into T_ORDER 
  SELECT  *
  FROM #temp_order_history_insert_loop;
 COMMIT tran;

 DROP TABLE #temp_order_history_insert_loop;
 --SELECT @count = count(0) FROM  T_ORDER_TEMP h WHERE EXISTS (SELECT 1  FROM #temp_order_history_insert WHERE id=h.ID)
 --and NOT EXISTS(SELECT 1 FROM T_ORDER ho WHERE ho.id=h.id)
 END;