优化存储过程，提高执行速度！

[doubagui]

if OBJECT_ID('SF_COUNT_MAX','P') IS NOT NULL
DROP PROC SF_COUNT_MAX
GO
CREATE PROC SF_COUNT_MAX @j NVARCHAR(5)
as
begin
DECLARE @SQL NVARCHAR(3000)
DECLARE @i int
set @i=1
set @SQL='update vw_comp100 set diff1=1,diff2=0'
exec(@SQL)
while @i<=cast(@j as int)
begin
set @SQL='update vw_comp100 set diff1=diff1+1
where D'+cast(@i as nvarchar(5))+'=D'+cast((@i+1) as nvarchar(5))+
' update vw_comp100 set diff2=diff1
where D'+cast(@i as nvarchar(5))+'!=D'+cast((@i+1) as nvarchar(5))+
' and diff2<diff1 '+
' update vw_comp100 set diff1=1
where D'+cast(@i as nvarchar(5))+'!=D'+cast((@i+1) as nvarchar(5))
exec(@SQL)
set @i=@i+1
end
set @SQL=' update vw_comp100 set diff2=diff1
where diff2<diff1 '
exec(@SQL)
END

现在这段代码执行速度较慢！@j为20时候，时间就要1分钟！怎么修改才使执行速度快！其中vw_comp100 为视图，@j值可到900

[--小F--]

都这样了 帮顶 等高手.

[doubagui]

补充：记录目前为5万条

[shizheyangde]

只能绑定

[doubagui]

[Quote=引用 3 楼 shizheyangde 的回复:]
只能绑定
[/Quote]？？？？？？？

[doubagui]

[Quote=引用 11 楼 sql_sf 的回复:]
SQL code
--try
if OBJECT_ID('SF_COUNT_MAX','P') IS NOT NULL
DROP PROC SF_COUNT_MAX
GO
CREATE PROC SF_COUNT_MAX @j NVARCHAR(5)
as
begin
DECLARE @SQL NVARCHAR(3000)
DECLARE @i int
set @i=1
……
[/Quote]
首先谢谢，速度提高了一些。不过还是慢！有没有更好的方式！

[doubagui]

有没有高手哦！

[Mr_Nice]

无从下手啊。等高手，学习...

[doubagui]

加分喽，来研究一下！

[Q315054403]

可改写以提升性能。。。有偿支持

[三断笛]

代码上除了换成CASE WHEN似乎没多大优化的余地了 vw_comp100是什么东西？数据量多大？有些什么表？索引是怎样的？

[doubagui]

没有办法

[sql_sf]

把里面的UPdate改成一个语句

[sql_sf]

--try
if OBJECT_ID('SF_COUNT_MAX','P') IS NOT NULL
DROP PROC SF_COUNT_MAX
GO
CREATE PROC SF_COUNT_MAX @j NVARCHAR(5)
as
begin
DECLARE @SQL NVARCHAR(3000)
DECLARE @i int
set @i=1  
set @SQL='update vw_comp100 set diff1=1,diff2=0'   
  exec(@SQL)   
  while @i<=cast(@j as int)
  begin  
  set @SQL='update vw_comp100 set diff1=case when D'+cast(@i as nvarchar(5))+'=D'+cast((@i+1) as nvarchar(5)) 
           +' then diff1+1 else 1 end,diff2= case when D'+cast(@i as nvarchar(5))
           +'!=D'+cast((@i+1) as nvarchar(5))+' and diff2<diff1 then diff1 else diff2 end'
  exec(@SQL)   
  set @i=@i+1   
  end   
  set @SQL=' update vw_comp100 set diff2=diff1
  where diff2<diff1 '   
  exec(@SQL)   
END
go
exec SF_COUNT_MAX 1

[htl258_Tony]

[Quote=引用 11 楼 sql_sf 的回复:]
SQL code
--try
if OBJECT_ID('SF_COUNT_MAX','P') IS NOT NULL
DROP PROC SF_COUNT_MAX
GO
CREATE PROC SF_COUNT_MAX @j NVARCHAR(5)
as
begin
DECLARE @SQL NVARCHAR(3000)
DECLARE @i int
set @i=1
……
[/Quote]可以这样试试，减少表的多次扫描。