谁能帮我把这个存储过程给优化一下啊 ! 数据量太大,执行速度太慢了! 谢谢!
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2 begin
-----删除活动表
while @TableName='活动表'
begin
select @sqlStr ='delete from '+@oldBaseName+'..活动表 '
select @sqlStr =@sqlStr +' where 人员编号 in (select 人员编号 from '+@newBaseName+'..活动表) and 内容 in(select 内容 from '+@newBaseName+'..活动表)
and 类别 in(select 类别 from '+@newBaseName+'..活动表)and 学科 in (select 学科 from '+@newBaseName+'..活动表)and 形式 in(select 形式 from '+@newBaseName+'..活动表)
and 学分 in (select 学分 from '+@newBaseName+'..活动表 ) and 学时 in (select 学时 from '+@newBaseName+'..活动表 )and 日期 in(select 日期 from '+@newBaseName+'..活动表) ' exec(@sqlStr)
break
endend
end
GO
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2 begin
-----删除活动表
while @TableName='活动表'
begin
select @sqlStr ='delete from '+@oldBaseName+'..活动表 '
select @sqlStr =@sqlStr +' where 人员编号 in (select 人员编号 from '+@newBaseName+'..活动表) and 内容 in(select 内容 from '+@newBaseName+'..活动表)
and 类别 in(select 类别 from '+@newBaseName+'..活动表)and 学科 in (select 学科 from '+@newBaseName+'..活动表)and 形式 in(select 形式 from '+@newBaseName+'..活动表)
and 学分 in (select 学分 from '+@newBaseName+'..活动表 ) and 学时 in (select 学时 from '+@newBaseName+'..活动表 )and 日期 in(select 日期 from '+@newBaseName+'..活动表) ' exec(@sqlStr)
break
endend
end
GO
IN-->EXISTS
--OR
IN-->INNER
换成INNER JOIN吧
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2
-----删除活动表
if ( @TableName='活动表' )
begin
select @sqlStr ='delete a from '+@oldBaseName+'..活动表 as a '
select @sqlStr =@sqlStr +'
where exists(select 1 from '+@newBaseName+'..活动表 as b
where a.人员编号 = b.人员编号 and a.内容 = b.内容 and a.类别 = b.类别 and
a.学科 = b.学科 and a.形式 = b.形式 and a.学分 = b.学分 and a.学时 = b.学时 and
a.日期 = b.日期) '
exec (@sqlStr)
end
end GO
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2
-----删除活动表
if ( @TableName='活动表' )
begin
select @sqlStr ='delete a from '+@oldBaseName+'..活动表 as a '
select @sqlStr =@sqlStr +'
where exists(select 1 from '+@newBaseName+'..活动表 as b
where a.人员编号 = b.人员编号 ) '
exec (@sqlStr)
end
end GO并在建一个人员编号索引
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2 begin if @TableName = '活动表'
BEGIN
SET @sqlstr = 'DELETE a FROM ' + @oldBaseName + '..活动表 a
INNER JOIN ' + @newBaseName + '..活动表 b
ON a.人员编号=b.人员编号
AND a.内容 = b.内容
AND a.分类 = b.分类
AND a.学科 = b.学科
AND a.学时 = b.学时
AND a.日期 = b.日期
AND a.形式 = b.形式'
EXEC(@sqlstr)
END
END
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
BEGIN
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2
if @TableName = '活动表'
BEGIN
SET @sqlstr = 'DELETE a FROM ' + @oldBaseName + '..活动表 a
INNER JOIN ' + @newBaseName + '..活动表 b
ON a.人员编号=b.人员编号
AND a.内容 = b.内容
AND a.分类 = b.分类
AND a.学科 = b.学科
AND a.学时 = b.学时
AND a.日期 = b.日期
AND a.形式 = b.形式'
EXEC(@sqlstr)
END
END