34,590
社区成员
发帖
与我相关
我的任务
分享
Declare @az table(NumberNo smallint,Amount smallint)
Insert Into @az(NumberNo,Amount) Select NumberNo,Amount from TMinit
--Select * from @az a,dbo.T_Pw_ABC_1(767) b Where a.NumberNo=b.Number And b.T1=1 And b.T2=8 And b.T3=5 这句处理不到 1秒钟
--Update @az Set Amount=1 Where NumberNoin(Select Number from dbo.T_Pw_ABC_1(767) Where Number=1 Or Number =2) 这句处理要 1分37秒钟
--Update @az Set Amount=1 Where NumberNo In(1850,1851,1852,1853,1854,1855,1856,1857,1858,1859) 这句处理不到 1秒钟
--Update @az Set Amount=1 from @az a,dbo.T_Pw_ABC_1(767) b Where a.NumberNo=b.Number And b.T1=1 And b.T2=8 And b.T3=5 这句处理要 1分35秒钟
Select * from @az
@az表有一万(10000)条数据,而 Select Number from dbo.T_Pw_ABC_1(767) Where Number=1 Or Number =2 查询得出 10 条数据(1850,1851,1852,1853,1854,1855,1856,1857,1858,1859)
有没有人帮我解释一下,为什么上面的四个句语所花的时间长短不一? 有没有办法优化以上的 Update 语句为运行时不超过3秒钟?
Declare @az table(NumberNo smallint,Amount smallint)
Insert Into @az(NumberNo,Amount) Select NumberNo,Amount from TMinit
Select Number into #tmp001 from dbo.T_Pw_ABC_1(767) Where T1=1 And T2=8 And T3=5
Update @az Set Amount=1 Where NumberNo in(select number from #tmp001)
drop table #tmp001
Select * from @az
--奇怪了,用临时表又很快,更新数据不用 1秒钟。
--为什么这么奇怪?能不能解释一下原理?
Select Number into # from dbo.T_Pw_ABC_1(767) Where Number=1 Or Number =2
Update @az Set Amount=1 Where NumberNo in(select number from #)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION T_Pw_ABC_1(@QS_AutoId smallint)
RETURNS Table
AS
Return(Select Number,A as T1,B as T2,C as T3 from ZM Where AutoId=@QS_AutoId)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Select Number from dbo.T_Pw_ABC_1(767) Where Number=1 Or Number =2 --的结果为(1850,1851,1852,1853,1854,1855,1856,1857,1858,1859) 十条数据 运行 不到 1秒钟。
Update @az Set Amount=1 Where NumberNo In(1850,1851,1852,1853,1854,1855,1856,1857,1858,1859) --这句处理不到 1秒钟
--但是,为什么下面这条语句要 1分37秒钟?
Update @az Set Amount=1 Where NumberNoin(Select Number from dbo.T_Pw_ABC_1(767) Where Number=1 Or Number =2) --这句处理要 1分37秒钟