应用场景为:
一台本地服务器ServerBD,一台远程服务器ServerYC.在本地服务器中将远程服务器建成了一个链接服务器。
远程数据库中的数据在不断增加中,现在需要隔一段时间将远程库中的数据导入本地库中,并删除远程库中己导的数据。我现在存储过程是这样写的:效率很低,并且不知道正确不正确,请高手指点一下。
USE [kycms]
GO
/****** 对象: StoredProcedure [dbo].[Link_ShanDong] 脚本日期: 08/11/2011 10:37:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Link_ShanDong]AS
--执行导入数据
beginINSERT INTO [kycms].[dbo].[Ky_WLXX](bhYuan,qh,lx,nr,dh,cfd,sj,laiyuan) select top 1000 bh,qh,lx,nr,dh,sheng+'-'+shi,sj,'sd' from [shandong].[smsinfo].[dbo].[toshandong2]end
--
begindelete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
end
--
一台本地服务器ServerBD,一台远程服务器ServerYC.在本地服务器中将远程服务器建成了一个链接服务器。
远程数据库中的数据在不断增加中,现在需要隔一段时间将远程库中的数据导入本地库中,并删除远程库中己导的数据。我现在存储过程是这样写的:效率很低,并且不知道正确不正确,请高手指点一下。
USE [kycms]
GO
/****** 对象: StoredProcedure [dbo].[Link_ShanDong] 脚本日期: 08/11/2011 10:37:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Link_ShanDong]AS
--执行导入数据
beginINSERT INTO [kycms].[dbo].[Ky_WLXX](bhYuan,qh,lx,nr,dh,cfd,sj,laiyuan) select top 1000 bh,qh,lx,nr,dh,sheng+'-'+shi,sj,'sd' from [shandong].[smsinfo].[dbo].[toshandong2]end
--
begindelete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
end
--
delete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
换成
;with cte as
(
select top 500 * from [shandong].[smsinfo].[dbo].[toshandong2]
)
delete from cte;
试试
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
end小改下,不要用in
order by bhend
--
begindelete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2] order by bh)end
效率问题,加索引是必须的,不过可能解决不了全部问题