当前位置： 移动技术网 > IT编程>数据库>MSSQL > sqlserver订单迁移的优化方案

sqlserver订单迁移的优化方案

2018年02月06日  | 移动技术网IT编程  | 我要评论

利君贿选,威海汽车站时刻表,齐鲁晚报广告

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

方案利用 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;

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网