/*if not object_id('NewRecord')is null drop table NewRecord go create table NewRecord(FILED0 varchar(20),FILED1 varchar(20), FILED2 varchar(20), FILED3 varchar(20)) go if not object_id('test')is null drop proc test go create proc test @para1 varchar(20), @para2 varchar(20), @para3 varchar(20), @para4 varchar(20) as declare @r int if exists(select 1 from NewRecord where FILED0=@para1 AND FILED1=@para2 AND FILED2=@para3 AND FILED3=@para3) set @r=1 else if exists(select 1 from NewRecord where FILED0=@para1 AND FILED1=@para2) set @r=2 else set @r=0 insert NewRecord select @para1,@para2,@para3,@para3 return @r*/ --truncate table NewRecord exec test 'A','B','C','D' select * from NewRecordFILED0 FILED1 FILED2 FILED3 -------------------- -------------------- -------------------- -------------------- A B C C A B C C A B C C你的代码没看到你如何控制重复的~~
drop table NewRecord
go
create table NewRecord(FILED0 varchar(20),FILED1 varchar(20), FILED2 varchar(20), FILED3 varchar(20))
go
if not object_id('test')is null
drop proc test
go
create proc test
@para1 varchar(20),
@para2 varchar(20),
@para3 varchar(20),
@para4 varchar(20)
as
declare @r int
if exists(select 1 from NewRecord where FILED0=@para1 AND FILED1=@para2 AND FILED2=@para3 AND FILED3=@para3)
set @r=1
else if exists(select 1 from NewRecord where FILED0=@para1 AND FILED1=@para2)
set @r=2
else
set @r=0
insert NewRecord select @para1,@para2,@para3,@para3
return @r*/
--truncate table NewRecord
exec test 'A','B','C','D'
select * from NewRecordFILED0 FILED1 FILED2 FILED3
-------------------- -------------------- -------------------- --------------------
A B C C
A B C C
A B C C你的代码没看到你如何控制重复的~~
优化方法:
1,对FILED0和FILED1 建立索引
2,在功能需求允许的情况下考虑将历史数据删除或转存到其他表当中
而且你每次都会在最后面执行那个insert 语句,貌似前面做的比对都是无用功
同时对你的存储过程表示怀疑