求助，使用游标速度太慢
ALTER proc [dbo].[p_multipleChoiceSplit]
as
declare @Code varchar(50),@s_chronic varchar(100),@s_CHSISRequire varchar(100),@s_HealthCheckProblem varchar(100)
---记录数组的长度
declare @length int,@next int
---对表2B-7，3A-4,3B-3b多选项进行拆分
declare cur_list cursor for select Code,IsHaveChronic,CHSISRequire,HealthCheckProblem from dbo.PeopleQuestion where IsHaveChronic!='' or CHSISRequire!='' or HealthCheckProblem!=''
begin
---建立拆分的多选项表
if not exists(select name from sys.tables where name='t_multipleChoiceSplit' and type='u')
create table t_multipleChoiceSplit(Code varchar(50) null,[Type] varchar(50) null ,[Option] varchar(50) null)
open cur_list
fetch next from cur_list into @Code,@s_chronic,@s_CHSISRequire,@s_HealthCheckProblem
while @@fetch_status=0
begin
---2B-7
if (@s_chronic!='')
begin
set @length=dbo.Get_StrArrayLength(@s_chronic,'.')
set @next=1
if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='IsHaveChronic')
begin
while @next<=@length
begin
insert into t_multipleChoiceSplit values(@Code,'IsHaveChronic',dbo.Get_StrArrayStrOfIndex(@s_chronic,'.',@next))
set @next=@next+1
end
end
end
----3A-4
if (@s_CHSISRequire!='')
begin
set @length=dbo.Get_StrArrayLength(@s_CHSISRequire,'.')
set @next=1
if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='CHSISRequire')
begin
while @next<=@length
begin
insert into t_multipleChoiceSplit values(@Code,'CHSISRequire',dbo.Get_StrArrayStrOfIndex(@s_CHSISRequire,'.',@next))
set @next=@next+1
end
end
end
----3B-3b
if(@s_HealthCheckProblem!='')
begin
set @length=dbo.Get_StrArrayLength(@s_HealthCheckProblem,'.')
set @next=1
if not exists(select Code from t_multipleChoiceSplit where Code=@Code and [Type]='HealthCheckProblem')
begin
while @next<=@length
begin
insert into t_multipleChoiceSplit values(@Code,'HealthCheckProblem',dbo.Get_StrArrayStrOfIndex(@s_HealthCheckProblem,'.',@next))
set @next=@next+1
end
end
end
fetch next from cur_list into @Code,@s_chronic,@s_CHSISRequire,@s_HealthCheckProblem
end
select distinct * into #t_multipleChoiceSplit from t_multipleChoiceSplit
delete from t_multipleChoiceSplit
insert into t_multipleChoiceSplit select * from #t_multipleChoiceSplit
close cur_list
deallocate cur_list
end
dbo.PeopleQuestion 表里有9W行数据，整个执行完要3个小时，有没有好办法优化一下或者不用游标会不会快点，实在太慢了，大家帮忙看看。
18 个解决方案
#1
这种程序，直接不要修改，扔掉。最多作为后人的一种参考。
给出需求，自己重新写
给出需求，自己重新写
#2
#3
最好把函数 Get_StrArrayLength 也贴出来.
#4
还有 Get_StrArrayStrOfIndex 函数,或其他尚未看到的函数.
#5
以下是两个函数，是不是函数效率太低
ALTER function [dbo].[Get_StrArrayLength]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
ALTER function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
#6
你用的是SQL Server 2000,还是sql server 2005?如果是后者,可以作较大改进,如果是前者,恐怕改善不大.
#7
是sql server 2005，帮忙看看
#8
好久没来了，绑定
#9
稍候.
#10
不知道你是不是要的这样的东西:
create table PeopleQuestion(Code int,IsHaveChronic nvarchar(100))
insert into PeopleQuestion select 1,'fao;ijf.foaedf.ojawoei.oijfawe.ojfas.sadfoij'
insert into PeopleQuestion select 2,'fawei.fiuawerfa.j9sadiu.jfaweo8u'
go
;with cte as(
select code,'IsHaveChronic' as type,
left(IsHaveChronic,charindex('.',IsHaveChronic)-1)as getstring,
right(IsHaveChronic,len(IsHaveChronic)-charindex('.',IsHaveChronic)) as leftstring,1 as rownumber
from PeopleQuestion where charindex('.',IsHaveChronic)>0
union all
select code,'IsHaveChronic' as type,
left(leftstring,charindex('.',leftstring)-1)as getstring,
right(leftstring,len(leftstring)-charindex('.',leftstring)) as leftstring,rownumber+1 as rownumber
from cte where charindex('.',leftstring)>0
)select code,type,getstring,rownumber from cte order by code,rownumber
go
drop table PeopleQuestion
/*
code type getstring rownumber
----------- ------------- --------------------------------------- -----------
1 IsHaveChronic fao;ijf 1
1 IsHaveChronic foaedf 2
1 IsHaveChronic ojawoei 3
1 IsHaveChronic oijfawe 4
1 IsHaveChronic ojfas 5
2 IsHaveChronic fawei 1
2 IsHaveChronic fiuawerfa 2
2 IsHaveChronic j9sadiu 3
(8 行受影响)
*/
#11
恩，谢谢，差不多就是我想要的结果了，如果是这样就对了，就是查字符串里有几个点如果有3个点代表有4个值
code type getstring rownumber
----------- ------------- --------------------------------------- -----------
1 IsHaveChronic fao;ijf 1
1 IsHaveChronic foaedf 2
1 IsHaveChronic ojawoei 3
1 IsHaveChronic oijfawe 4
1 IsHaveChronic ojfas 5
1 IsHaveChronic sadfoij 6
2 IsHaveChronic fawei 1
2 IsHaveChronic fiuawerfa 2
2 IsHaveChronic j9sadiu 3
2 IsHaveChronic jfaweo8u 4
#12
如果没有点是不是不计算在内?
#13
没有点的话就算一个值
create table PeopleQuestion(Code int,IsHaveChronic nvarchar(100))
insert into PeopleQuestion select 1,'fao;ijf.foaedf.ojawoei.oijfawe.ojfas.sadfoij'
insert into PeopleQuestion select 2,'fawei.fiuawerfa.j9sadiu.jfaweo8u'
insert into PeopleQuestion select 3,'uuu'
code type getstring rownumber
----------- ------------- --------------------------------------- -----------
1 IsHaveChronic fao;ijf 1
1 IsHaveChronic foaedf 2
1 IsHaveChronic ojawoei 3
1 IsHaveChronic oijfawe 4
1 IsHaveChronic ojfas 5
1 IsHaveChronic sadfoij 6
2 IsHaveChronic fawei 1
2 IsHaveChronic fiuawerfa 2
2 IsHaveChronic j9sadiu 3
2 IsHaveChronic jfaweo8u 4
3 IsHaveChronic uuu 1
#14
create table PeopleQuestion(Code int,IsHaveChronic nvarchar(150),CHSISRequire nvarchar(150),HealthCheckProblem nvarchar(150))
insert into PeopleQuestion select 1,'fao;ijf.foaedf.ojawoei.oijfawe.ojfas.sadfoij','nioujniased.nufaeua.o9weois.jo9jasd','viusd4.f0jasd.jdjhawd.oijoasdf.joijo9'
insert into PeopleQuestion select 2,'fawei.fiuawerfa.j9sadiu.jfaweo8u','vniuzsd.foiujawsed','voiusad.fjoiujasd.fwuehksudf.foisjadf'
go
;with cte as(
select code,'IsHaveChronic' as type,
convert(nvarchar(50),left(IsHaveChronic,charindex('.',IsHaveChronic)-1))as getstring,
convert(nvarchar(150),right(IsHaveChronic,len(IsHaveChronic)-charindex('.',IsHaveChronic))+'.') as leftstring,1 as rownumber
from PeopleQuestion where charindex('.',IsHaveChronic)>0
union all
select code,'CHSISRequire' as type,
convert(nvarchar(50),left(CHSISRequire,charindex('.',CHSISRequire)-1))as getstring,
convert(nvarchar(150),right(CHSISRequire,len(CHSISRequire)-charindex('.',CHSISRequire))+'.') as leftstring,1 as rownumber
from PeopleQuestion where charindex('.',CHSISRequire)>0
union all
select code,'HealthCheckProblem' as type,
convert(nvarchar(50),left(HealthCheckProblem,charindex('.',HealthCheckProblem)-1))as getstring,
convert(nvarchar(150),right(HealthCheckProblem,len(HealthCheckProblem)-charindex('.',HealthCheckProblem))+'.') as leftstring,1 as rownumber
from PeopleQuestion where charindex('.',HealthCheckProblem)>0
union all
select code,type,
convert(nvarchar(50),left(leftstring,charindex('.',leftstring)-1))as getstring,
right(leftstring,len(leftstring)-charindex('.',leftstring)) as leftstring,rownumber+1 as rownumber
from cte where len(leftstring)>1
)select code,type,getstring,rownumber from cte order by code,type,rownumber
go
drop table PeopleQuestion
/*
code type getstring rownumber
----------- ------------------ -------------------------------------------------- -----------
1 CHSISRequire nioujniased 1
1 CHSISRequire nufaeua 2
1 CHSISRequire o9weois 3
1 CHSISRequire jo9jasd 4
1 HealthCheckProblem viusd4 1
1 HealthCheckProblem f0jasd 2
1 HealthCheckProblem jdjhawd 3
1 HealthCheckProblem oijoasdf 4
1 HealthCheckProblem joijo9 5
1 IsHaveChronic fao;ijf 1
1 IsHaveChronic foaedf 2
1 IsHaveChronic ojawoei 3
1 IsHaveChronic oijfawe 4
1 IsHaveChronic ojfas 5
1 IsHaveChronic sadfoij 6
2 CHSISRequire vniuzsd 1
2 CHSISRequire foiujawsed 2
2 HealthCheckProblem voiusad 1
2 HealthCheckProblem fjoiujasd 2
2 HealthCheckProblem fwuehksudf 3
2 HealthCheckProblem foisjadf 4
2 IsHaveChronic fawei 1
2 IsHaveChronic fiuawerfa 2
2 IsHaveChronic j9sadiu 3
2 IsHaveChronic jfaweo8u 4
(25 行受影响)
*/
#15
没点算一行:
create table PeopleQuestion(Code int,IsHaveChronic nvarchar(150),CHSISRequire nvarchar(150),HealthCheckProblem nvarchar(150))
insert into PeopleQuestion select 1,'fao;ijf.foaedf.ojawoei.oijfawe.ojfas.sadfoij','nioujniased.nufaeua.o9weois.jo9jasd','viusd4.f0jasd.jdjhawd.oijoasdf.joijo9'
insert into PeopleQuestion select 2,'fawei.fiuawerfa.j9sadiu.jfaweo8u','vniuzsd.foiujawsed','voiusad.fjoiujasd.fwuehksudf.foisjadf'
insert into PeopleQuestion select 3,'foijoasd','vniuasd','iuweu'
go
;with cte as(
select code,'IsHaveChronic' as type,
convert(nvarchar(50),left(IsHaveChronic+'.',charindex('.',IsHaveChronic+'.')-1))as getstring,
convert(nvarchar(150),right(IsHaveChronic+'.',len(IsHaveChronic+'.')-charindex('.',IsHaveChronic+'.'))) as leftstring,1 as rownumber
from PeopleQuestion where IsHaveChronic!=''
union all
select code,'CHSISRequire' as type,
convert(nvarchar(50),left(CHSISRequire+'.',charindex('.',CHSISRequire+'.')-1))as getstring,
convert(nvarchar(150),right(CHSISRequire+'.',len(CHSISRequire+'.')-charindex('.',CHSISRequire+'.'))) as leftstring,1 as rownumber
from PeopleQuestion where CHSISRequire!=''
union all
select code,'HealthCheckProblem' as type,
convert(nvarchar(50),left(HealthCheckProblem+'.',charindex('.',HealthCheckProblem+'.')-1))as getstring,
convert(nvarchar(150),right(HealthCheckProblem+'.',len(HealthCheckProblem+'.')-charindex('.',HealthCheckProblem+'.'))) as leftstring,1 as rownumber
from PeopleQuestion where HealthCheckProblem!=''
union all
select code,type,
convert(nvarchar(50),left(leftstring,charindex('.',leftstring)-1))as getstring,
right(leftstring,len(leftstring)-charindex('.',leftstring)) as leftstring,rownumber+1 as rownumber
from cte where len(leftstring)>1
)select code,type,getstring,rownumber from cte order by code,type,rownumber
go
drop table PeopleQuestion
/*
code type getstring rownumber
----------- ------------------ -------------------------------------------------- -----------
1 CHSISRequire nioujniased 1
1 CHSISRequire nufaeua 2
1 CHSISRequire o9weois 3
1 CHSISRequire jo9jasd 4
1 HealthCheckProblem viusd4 1
1 HealthCheckProblem f0jasd 2
1 HealthCheckProblem jdjhawd 3
1 HealthCheckProblem oijoasdf 4
1 HealthCheckProblem joijo9 5
1 IsHaveChronic fao;ijf 1
1 IsHaveChronic foaedf 2
1 IsHaveChronic ojawoei 3
1 IsHaveChronic oijfawe 4
1 IsHaveChronic ojfas 5
1 IsHaveChronic sadfoij 6
2 CHSISRequire vniuzsd 1
2 CHSISRequire foiujawsed 2
2 HealthCheckProblem voiusad 1
2 HealthCheckProblem fjoiujasd 2
2 HealthCheckProblem fwuehksudf 3
2 HealthCheckProblem foisjadf 4
2 IsHaveChronic fawei 1
2 IsHaveChronic fiuawerfa 2
2 IsHaveChronic j9sadiu 3
2 IsHaveChronic jfaweo8u 4
3 CHSISRequire vniuasd 1
3 HealthCheckProblem iuweu 1
3 IsHaveChronic foijoasd 1
(28 行受影响)
*/
#16
非常感谢，我去试试
#17
不一定是游标的问题
建议你在各个步骤print出时间,检查到底是哪里的速度慢
我遇到过同样的事情,问题是我的游标里的和你类似的 if exists或者if not exists的语句, 在表记录膨胀的时候,N万条数据的循环就超级的慢了
建议你在各个步骤print出时间,检查到底是哪里的速度慢
我遇到过同样的事情,问题是我的游标里的和你类似的 if exists或者if not exists的语句, 在表记录膨胀的时候,N万条数据的循环就超级的慢了
#18
凡是在游标里加循环什么的,如果你采用的是2005,那绝对要想办法摒弃游标,以换查询效率.
智能推荐
注意！
本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。