求一数据导入删除的存储过程

[aspxcss]

应用场景为：
一台本地服务器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
--执行导入数据


begin

INSERT 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
--
begin

delete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
end
--

[--小F--]

IN后面再跟子查询 效率不会高

[geniuswjt]

好像是这样，等高手现身

[快溜]

bh 建索引试试

[gw6328]

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;
试试

[geniuswjt]

delete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2])
end

小改下，不要用in

[NBDBA]

至少要加Order by

begin

INSERT 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]
order by bh

end
--
begin

delete from [shandong].[smsinfo].[dbo].[toshandong2]
where bh in(select top 500 bh from [shandong].[smsinfo].[dbo].[toshandong2] order by bh)

end


效率问题，加索引是必须的，不过可能解决不了全部问题