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;