set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
declare @name varchar(50),
@tel1 varchar(50),
@tel2 varchar(50),
@tel3 varchar(50),
@birth varchar(50)
declare MyCursor1 cursor local FORWARD_ONLY STATIC READ_ONLY for select name,tel1,tel2,tel3,birth from tbl_Info2
Open MyCursor1
begin
FETCH next FROM MyCursor1 INTO @name, @tel1, @tel2, @tel3,@birth
While (@@fetch_status=0)
begin
if not exists (select * from tbl_Info where ((tbl_Info.name=@name and (tbl_Info.tel1=@tel1 or tbl_Info.tel1=@tel2 or tbl_Info.tel1=@tel3 or tbl_Info.tel2=@tel3)) or (tbl_Info.birth=@birth and (tbl_Info.tel1=@tel1 or tbl_Info.tel1=@tel2 or tbl_Info.tel1=@tel3 or tbl_Info.tel2=@tel3))))
begin
insert into tbl_Info (Tel1,tel2,tel3,name,birth ) values(@tel1,@tel2,@tel3,@name,@birth)
end
else
begin
insert into tbl_InfoChongfu (Tel1,tel2,tel3,name,birth ) values(@tel1,@tel2,@tel3,@name,@birth)
end
FETCH next FROM MyCursor1 INTO @name, @tel1, @tel2, @tel3,@birth
end
end
delete from tbl_Info2
END这段存储过程主要目的就是从tbl_Info2中往tbl_Info表中插数据,满足不重复条件的进tbl_Info表,否则进tbl_InfoChongfu,
每次大概插入1W5的数据,在tbl_Info表数据量小的时候还行,可是当tbl_Info表中数据大于50W的时候就一直超时,谁能帮我解决下
set QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN
declare @name varchar(50),
@tel1 varchar(50),
@tel2 varchar(50),
@tel3 varchar(50),
@birth varchar(50)
declare MyCursor1 cursor local FORWARD_ONLY STATIC READ_ONLY for select name,tel1,tel2,tel3,birth from tbl_Info2
Open MyCursor1
begin
FETCH next FROM MyCursor1 INTO @name, @tel1, @tel2, @tel3,@birth
While (@@fetch_status=0)
begin
if not exists (select * from tbl_Info where ((tbl_Info.name=@name and (tbl_Info.tel1=@tel1 or tbl_Info.tel1=@tel2 or tbl_Info.tel1=@tel3 or tbl_Info.tel2=@tel3)) or (tbl_Info.birth=@birth and (tbl_Info.tel1=@tel1 or tbl_Info.tel1=@tel2 or tbl_Info.tel1=@tel3 or tbl_Info.tel2=@tel3))))
begin
insert into tbl_Info (Tel1,tel2,tel3,name,birth ) values(@tel1,@tel2,@tel3,@name,@birth)
end
else
begin
insert into tbl_InfoChongfu (Tel1,tel2,tel3,name,birth ) values(@tel1,@tel2,@tel3,@name,@birth)
end
FETCH next FROM MyCursor1 INTO @name, @tel1, @tel2, @tel3,@birth
end
end
delete from tbl_Info2
END这段存储过程主要目的就是从tbl_Info2中往tbl_Info表中插数据,满足不重复条件的进tbl_Info表,否则进tbl_InfoChongfu,
每次大概插入1W5的数据,在tbl_Info表数据量小的时候还行,可是当tbl_Info表中数据大于50W的时候就一直超时,谁能帮我解决下
ALTER PROCEDURE [dbo].[test1]
AS
BEGIN insert into tbl_InfoChongfu (Tel1,tel2,tel3,name,birth )
select Tel1,tel2,tel3,name,birth
from tbl_Info2
where exists (
select *
from tbl_Info
where ((tbl_Info.name=tbl_Info2.name and (tbl_Info.tel1=tbl_Info2.tel1 or tbl_Info.tel1=tbl_Info2.tel2
or tbl_Info.tel1=tbl_Info2.tel3 or tbl_Info.tel2=tbl_Info2.tel3)) or (tbl_Info.birth=tbl_Info2.birth
and (tbl_Info.tel1=tbl_Info2.tel1 or tbl_Info.tel1=tbl_Info2.tel2 or tbl_Info.tel1=tbl_Info2.tel3
or tbl_Info.tel2=tbl_Info2.tel3)))) insert into tbl_Info (Tel1,tel2,tel3,name,birth )
select Tel1,tel2,tel3,name,birth
from tbl_Info2
where not exists (
select *
from tbl_Info
where ((tbl_Info.name=tbl_Info2.name and (tbl_Info.tel1=tbl_Info2.tel1 or tbl_Info.tel1=tbl_Info2.tel2
or tbl_Info.tel1=tbl_Info2.tel3 or tbl_Info.tel2=tbl_Info2.tel3)) or (tbl_Info.birth=tbl_Info2.birth
and (tbl_Info.tel1=tbl_Info2.tel1 or tbl_Info.tel1=tbl_Info2.tel2 or tbl_Info.tel1=tbl_Info2.tel3
or tbl_Info.tel2=tbl_Info2.tel3))))
END
tbl_Info (name,tel1,tel2,tel3)
tbl_Info (birth,tel1,tel2,tel3)
tbl_Info2 (name,tel1,tel2,tel3)
tbl_Info2 (birth,tel1,tel2,tel3)
*
from
tbl_Info
where
(tbl_Info.name=@name or tbl_Info.birth=@birth)
and
(tbl_Info.tel1=@tel1 or tbl_Info.tel1=@tel2 or tbl_Info.tel1=@tel3 or tbl_Info.tel2=@tel3)
--------------------------------------------------------------------------------超时时间已到。在操作完成之前超时时间已过或服务器未响应。
语句已终止。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。
语句已终止。源错误:
行 114: catch (Exception ex)
行 115: {
行 116: throw ex;
行 117: }
行 118: finally
源文件: f:\web\telManager\admin\Excel.aspx.cs 行: 116 堆栈跟踪:
[SqlException (0x80131904): 超时时间已到。在操作完成之前超时时间已过或服务器未响应。
语句已终止。]
admin_Excel.Button1_Click(Object sender, EventArgs e) in f:\web\telManager\admin\Excel.aspx.cs:116
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4921
--------------------------------------------------------------------------------
版本信息: Microsoft .NET Framework 版本:2.0.50727.42; ASP.NET 版本:2.0.50727.42 2位前辈的方法都试了,可还是报这错误
select
*
from
tbl_Info
where
(tbl_Info.name=@name or tbl_Info.birth=@birth)
and
(tbl_Info.tel1 in(@tel1,@tel2,@tel3)or tbl_Info.tel2=@tel3)
select
*
from
tbl_Info
where
(tbl_Info.name=@name or tbl_Info.birth=@birth)
and
(tbl_Info.tel1 in(@tel1,@tel2,@tel3)or tbl_Info.tel2=@tel3)