22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE SP_Insert
(
@ID INT,
@value1 INT,
@value2 INT
)
AS
BEGIN
--这里 是插入数据的业务逻辑 这里业务逻辑比较复杂 目前难于优化
--每次执行大概需要插入10条记录
--INSERT INTO
END
CREATE PROCEDURE SP_Select_Insert
(
@value1 INT,
@value2 INT
)
AS
BEGIN
DECLARE @SIDs TABLE(ID INT)
BEGIN
INSERT INTO @SIDs
SELECT ID FROM TABLE1 --这里有10万左右的数据
END
DECLARE c CURSOR FAST_FORWARD FOR SELECT ID FROM @SIDs
OPEN c
DECLARE @Id INT
FETCH NEXT FROM c INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC SP_Insert @Id,@value1,@value2
FETCH NEXT FROM c INTO @Id
END
CLOSE c
DEALLOCATE c
END
--SP_Select_Insert '1','2'
--10*10万条左右的数据 总耗时大概 25 分钟 汗!
第一:
DECLARE @maxRow INT
DECLARE @SIDs TABLE(ID INT)
换成 创建 临时表 :create table #SIDs (uID INT identity(1,1),id int primary key(uid))
INSERT INTO #SIDs (id)
SELECT ID FROM TABLE1 --这里有10万左右的数据
select @maxRow = @@rowcount
第二:
不申明游标:DECLARE c CURSOR FAST_FORWARD FOR SELECT ID FROM @SIDs
set @i = 1
while (@i <= @maxRow)
begin
select id from #SIDs where uid = @i
EXEC SP_Insert
set @i = @i+1
end