拆很长很长的字符串,速度问题

晓风残月0110 2008-03-15 03:05:27


//我的方法
declare @centers varchar(max)
set @centers='1,2,3,4,5,6,'
while len(@centers)>0
begin
print left(@centers,charindex(',',@centers)-1)
--取出每个之后想做删除操作
--delete from t where ID = ?
set @centers=right(@centers,len(@centers)-charindex(',',@centers))
end

//问题
数据可能会8000-10000,
现在速度慢,大约5-6秒
怎样给他提提速,可以到2秒左右就比较满意了




//问题
数据可能会8000-10000,
现在速度慢,大约5-6秒
怎样给他提提速,可以到2秒左右就比较满意了
...全文
395 34 打赏 收藏 转发到动态 举报
写回复
用AI写文章
34 条回复
切换为时间正序
请发表友善的回复…
发表回复
晓风残月0110 2008-03-17
  • 打赏
  • 举报
回复
测试的字符串是1位数,
二位数
三位数
四位数
速度会有影响,
我实用1-8000
测试的速度是6s
晓风残月0110 2008-03-17
  • 打赏
  • 举报
回复
好思路
正在测试 do熊的方法
预计速度不会有问题
zheninchangjiang 2008-03-17
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 charry0110 的回复:]
我使用的是05
每个数字算是一条
然后测试大约8000条的数据,
字符串长度大概要是20000多
[/Quote]
20000多,感觉不奇,还想加快速度,那就加大内存,加快处理器
pt1314917 2008-03-17
  • 打赏
  • 举报
回复
楼主的代码我试过了。也只要1秒噢。。
dobear_0922 2008-03-17
  • 打赏
  • 举报
回复
declare @centers varchar(max), @xml xml
set @centers='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18'

select @centers=replace(@centers, ',', '</node><node>')
,@xml='<node>'+@centers+'</node>'

declare @dt datetime
set @dt=getdate()
select x.query('text()') as Nums from @xml.nodes('//node') as t(x)
print datediff(ms, @dt, getdate())

/*
(2772 row(s) affected)
216
*/




晓风残月0110 2008-03-17
  • 打赏
  • 举报
回复
使用了
delete from t where charindex(','+id+',',','+@str+',')> 0

直接删除根本就执行不完
晓风残月0110 2008-03-17
  • 打赏
  • 举报
回复
大家帮忙速度还是不行

如果我的字符串是从1到8000
使用

declare @centers varchar(max)
set @centers='1,2,3,4,5,6,'
declare @i int
set @i = charindex(',',@centers)
while @i>=1
begin
print left(@centers,@i-1)
--set @centers=right(@centers,len(@centers)-@i)
set @centers=substring(@centers, @i + 1, len(@centers) - @i)
set @i = charindex(',', @centers)
end
--6s,8000条

如果我的数据是8000个1
就只用3s
rowanshark 2008-03-17
  • 打赏
  • 举报
回复
我是刚注册的用户,现在来虚心学习中
dobear_0922 2008-03-17
  • 打赏
  • 举报
回复
最好把换行符也给替换成空,如果你的字符串包含换行。
晓风残月0110 2008-03-17
  • 打赏
  • 举报
回复
最后方案,依据do熊的方法
将变量更换为临时表,速度快且稳定

declare @centers varchar(max), @xml xml
set @centers='1,2,3,4,5,6,7,8,9'
declare @dt datetime
set @dt=getdate()
select @centers=replace(@centers,',','"></Xmlta><Xmlta node="'),
@xml='<Xmlta node="'+@centers+'"></Xmlta>'
create table #xmlt (xCol xml)
insert into #xmlt select @xml
delete from tempPersonInfo where Person_ID in (
SELECT nref.value('@node','varchar(16)')
FROM #xmlt CROSS APPLY xCol.nodes('/Xmlta') AS R(nref))
drop table #xmlt
print datediff(ms, @dt, getdate())
--测试结果8000条480ms
--测试结果20000条1000ms-1800ms

-晴天 2008-03-15
  • 打赏
  • 举报
回复
赋值两次字节数少的变量的时间比赋值一次varchar(8000)的变量的时间长多了!

declare @d1 datetime,@d2 datetime
declare @i int,@j int --改成smallint bigint,用时差不多
set @d1=getdate()
set @i=0
while @i<len(@centers)
begin
set @j=charindex(',',right(@centers,len(@centers)-@i))+@i
print substring(@centers,@i+1,@j-@i-1)
set @i=@j
end

set @d2 =getdate()

print '耗时:'+rtrim(datediff(ms,@d1,@d2))+'毫秒'
-晴天 2008-03-15
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 JiangHongTao 的回复:]
1.1G 迅驰 512G 内存
[/Quote]

My God!

512 GGGGGGGGG ?
晓风残月0110 2008-03-15
  • 打赏
  • 举报
回复
大家还有没有其他的方法可以一试的呢

等待好消息
  • 打赏
  • 举报
回复
这里的学习精神不错,不像WEB板块那些都是一些懒家伙在那

楼主学习精神不错

这样进步才快
晓风残月0110 2008-03-15
  • 打赏
  • 举报
回复
使用老乌龟给的函数

加了一个变量,@i,和一个substring


declare @centers varchar(max)
set @centers='1,2,3,4,5,6,'
declare @i int
set @i = charindex(',',@centers)
while @i>=1
begin
print left(@centers,@i-1)
--set @centers=right(@centers,len(@centers)-@i)
set @centers=substring(@centers, @i + 1, len(@centers) - @i)
set @i = charindex(',', @centers)
end
--3s,8000条


把 charindex(',', @centers) 都化成@i少了2s
把right换成substring少了1s
晓风残月0110 2008-03-15
  • 打赏
  • 举报
回复
To:楼上

可以试一试的你的方法啊,
我上面的测试还没有做删除
只拆串,然后打印的时间
zheninchangjiang 2008-03-15
  • 打赏
  • 举报
回复
大概是因为你的删除操作慢吧,
为什么不能这样呢
delete from t where charindex(','+id+',',','+@str+',')>0
dawugui 2008-03-15
  • 打赏
  • 举报
回复
/*
功能:实现split功能的函数
*/

create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return
end
go

--调用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split





JiangHongTao 2008-03-15
  • 打赏
  • 举报
回复
declare @d1 datetime,@d2 datetime,@i int
declare @centers varchar(8000)
declare @s varchar(8000)
select @i = 1,@centers = ''
while @i<=4000
select @centers = @centers+'1,',@i = @i+1
set @s = @centers
set @d1 =getdate()
set @i = 0
while len(@centers)>0
begin
--print left(@centers,charindex(',',@centers)-1)
set @i = @i+1
--取出每个之后想做删除操作
--delete from t where ID = ?
set @centers=right(@centers,len(@centers)-charindex(',',@centers))
end
set @d2 =getdate()
select '长度:'+rtrim(len(@s)) mess
union select '条数:'+rtrim(@i)
union select '耗时:'+rtrim(datediff(ms,@d1,@d2))+'毫秒'
/*
mess
---------------------
长度:8000
耗时:630毫秒
条数:4000
*/
JiangHongTao 2008-03-15
  • 打赏
  • 举报
回复
主要是PRINT 耗时。
加载更多回复(14)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧