62,046
社区成员
发帖
与我相关
我的任务
分享
if(not exists(select UserID from UserInfo where UserID='U0001'))
INsert INTO UserInfo....
if(not exists(select UserID from UserInfo where UserID='U0002'))
INsert INTO UserInfo....
-------------临时接插入耗时测试
declare @i int
set @i=0
--创建临时表
select 0 ID, 'UserName1' UserName into #TestUser where 1=2
--插入到临时表
while(@i<10000)
begin
Insert into #TestUser select @i, 'UserName1'
set @i=@i+1
end
--从临时表插入到正式表
insert TestUser(ID,UserName) select * from #TestUser where not exists(select 1 from TestUser where ID=#TestUser.ID)
drop table #TestUser
--(耗时:14秒)
---创建表
CREATE TABLE [dbo].[TestUser](
[ID] [int] NOT NULL,
[UserName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_TestUser] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-------------临时接插入耗时测试
declare @i int
set @i=0
--创建临时表
select 0 ID, 'UserName1' UserName into #TestUser where 1=2
--插入到临时表
while(@i<10000)
begin
Insert into #TestUser select @i, 'UserName1'
set @i=@i+1
end
--从临时表插入到正式表
insert TestUser(ID,UserName) select * from #TestUser where not exists(select 1 from TestUser where ID=#TestUser.ID)
drop table #TestUser
--(耗时:14秒)
--直接插入耗时测试
declare @j int
set @j=0
while(@j<10000)
begin
Insert into dbo.TestUser select @j, 'UserName1' where not exists(select 1 from TestUser where ID=@j)
set @j=@j+1
end
--(耗时:4秒)
--如果是第二次插入重复的数据时,用临时表更慢
还是产生sql语句
--创建表
CREATE TABLE [dbo].[TestUser](
[ID] [int] NOT NULL,
[UserName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_TestUser] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
--插入语句(不重复测试)
Insert into dbo.TestUser select 1, 'UserName1' where not exists(select 1 from TestUser where ID=1)
Insert into dbo.TestUser select 2, 'UserName2' where not exists(select 1 from TestUser where ID=2)
Insert into dbo.TestUser select 3, 'UserName3' where not exists(select 1 from TestUser where ID=3)
Insert into dbo.TestUser select 4, 'UserName4' where not exists(select 1 from TestUser where ID=4)
Insert into dbo.TestUser select 5, 'UserName5' where not exists(select 1 from TestUser where ID=5)
--插入语句(重复测试)
Insert into dbo.TestUser select 1, 'UserName1' where not exists(select 1 from TestUser where ID=1)
Insert into dbo.TestUser select 2, 'UserName2' where not exists(select 1 from TestUser where ID=2)
Insert into dbo.TestUser select 3, 'UserName3' where not exists(select 1 from TestUser where ID=3)
Insert into dbo.TestUser select 4, 'UserName4' where not exists(select 1 from TestUser where ID=4)
Insert into dbo.TestUser select 5, 'UserName5' where not exists(select 1 from TestUser where ID=5)