存储过程如何优化，求建议

Question

各位大虾好。

小弟第一次发帖。求回帖答复。

我有个同步过程需要从服务器1的表1同步到服务器2的表3，每个表的数据都比较大，20万条以上。但问题出现了，每次同步完成的时间都在12小时左右。我想通过优化达到减少时间的目的。

我听说使用游标效率会比较低，是不是这样呢。

如果知道的，望贴下代码，谢谢了。

以下是我的代码，语法没错，就是效率比较低。

CREATE PROCEDURE [dbo].[XIAOSHOU_LINE1] AS


declare @RECOUNT INT


declare @ORDER_NUMBER NUMERIC,@ORDERED_ITEM VARCHAR(50),@ORDERED_QUANTITY VARCHAR(20), @NAME VARCHAR(150),@LIST_HEADER_ID VARCHAR(200),@PACKAGE_ITEM_NUMBER VARCHAR(30)


DECLARE XIAOSHOU_LINE1 CURSOR FOR SELECT ORDER_NUMBER,ORDERED_ITEM,ORDERED_QUANTITY,NAME,LIST_HEADER_ID,PACKAGE_ITEM_NUMBER
FROM  服务器1数据库1表1 AS A WHERE EXISTS (SELECT TOP 300 ORDER_NUMBER FROM 服务器1数据库1表2 WHERE ORDER_NUMBER=A.ORDER_NUMBER ORDER BY CREATION_DATE DESC)


OPEN XIAOSHOU_LINE1
FETCH NEXT FROM XIAOSHOU_LINE1 INTO @ORDER_NUMBER,@ORDERED_ITEM,@ORDERED_QUANTITY, @NAME ,@LIST_HEADER_ID,@PACKAGE_ITEM_NUMBER


while @@fetch_status=0
BEGIN
IF EXISTS(SELECT * FROM 服务器2数据库2表3 WHERE 订单编号=@ORDER_NUMBER AND 商品编码=@ORDERED_ITEM AND 商品数量=@ORDERED_QUANTITY order by id desc)
BEGIN
SELECT @RECOUNT=(SELECT COUNT(ID) FROM 服务器2数据库2表3 WHERE 订单编号=@ORDER_NUMBER AND 商品编码=@ORDERED_ITEM AND 商品数量=@ORDERED_QUANTITY AND 价目表名称= @NAME AND 价目表ID=@LIST_HEADER_ID AND 虚拟编码=@PACKAGE_ITEM_NUMBER)
if @RECOUNT<1
BEGIN
UPDATE 服务器2数据库2表3 SET 商品数量=@ORDERED_QUANTITY,价目表名称= @NAME ,价目表ID=@LIST_HEADER_ID,虚拟编码=@PACKAGE_ITEM_NUMBER where 订单编号=@ORDER_NUMBER AND 商品编码=@ORDERED_ITEM AND 商品数量=@ORDERED_QUANTITY
END
END
ELSE
BEGIN
INSERT 服务器2数据库2表3 (订单编号,商品编码,商品数量,价目表名称,价目表ID,虚拟编码) VALUES (@ORDER_NUMBER,@ORDERED_ITEM,@ORDERED_QUANTITY, @NAME ,@LIST_HEADER_ID,@PACKAGE_ITEM_NUMBER)
END
fetch next from XIAOSHOU_LINE1 INTO @ORDER_NUMBER,@ORDERED_ITEM,@ORDERED_QUANTITY, @NAME ,@LIST_HEADER_ID,@PACKAGE_ITEM_NUMBER
END


CLOSE XIAOSHOU_LINE1
DEALLOCATE XIAOSHOU_LINE1
GO

Answer 1

回复
我用的就是这两个啊，只是我里面有游标。 我没懂你说的update,insert 是如何用的

Answer 2

回复
一条update，一条Insert啊

Answer 3

哪两条SQL语句啊

Answer 4

最主要的是你思路绕了一大圈。。。做了很多无谓的查询比较，将所有的数据提取出来，然后一条数据一条数据的和需要同步的数据库数据比较，有就更新没有就做插入操作。。你不觉得查询比较很耗时间么？

你不会将两个数据库的相同数据（key）的提取出来然后更新到备份数据库么，主数据库有的数据而备份数据库没有的数据插入到备份数据库么。。多简单省事哈，或者直接点将备份数据库中那些主数据库中存在的数据（key）删掉，将整个主数据库数据插入到备份数据库 insert into select。。。完事。。两条sql

Answer 5

我现在做的就是你说的第一种方法：两数据库都有相同数据的就更新、主数据库有、备份的没有，就进行插入操作。只是用游标比较慢。 至于你说的第二种，先在备份数据库删掉相同的数据，在把主数据库数据全查到备份数据库里，是不是一条delete，一条insert，两条SQL？

Answer 6

回复
update a set a.xxx = b.yyyy and a.zzz = b.llll from a join b on a.asdfasfs = b.sfsfsdfa and a.dfsafsadfas = b.dfasdfdf

Answer 7

为啥要用游标？直接两个SQL语句就行了啊。游标多费劲。

Answer 8

你复制10条数据试试多长时间，看看问题是不是出在数据量上。

如果10条需要1个小时的话，可能问题就不在你的语句上了

也许是网络，也许是异构数据库平台之间通信的开销 影响的

找到根源后再下手

Answer 9

才20万，用这么长时间。。。。网络怎么样？

Answer 10

补充：是从服务器1上的oracle数据表1的数据 同步到 服务器2上的sql server数据表2上。

更新12小时太久了，而且数据每天都会增加，更新时间会更久。

Answer 11

MSSQL不是有批量插入的功能吗？.NET支持

Answer 12

关注一下

同步的间隔时间是多少？

设置在什么时间进行？

对于优化我不太懂，但是如果数据库离得不远的话，不至于这么慢吧

Answer 13

这样你看成不。服务器2直接建立一个临时表，把1要更新的数据先放2的临时表里面。然后再用Oracle专用的同步更新语法更新。

Answer 14

没人回答吗