updatetext + stored procedure

[DotNetGnat]

Guys, i have a text field and inside the text field i have a portion that looks like:

<!--blah blah blah-->

i want to update that portion to

<!--DotNetGnat-->

i came up with the below stored procedure and it works fine...but takes a lot of time to complete...can you guys suggest anything else for better performance...

CREATE   Procedure ResumeTextCleanup
	@WBS1 varchar(30)='%'
	,@DescCategory varchar(10)='%'

AS

Declare @startpos int
	,@endpos int
        ,@ptr binary(16)
	,@commentlen int
	,@adjuststart int

Declare MyCursor CURSOR for
select WBS1, DescCategory from mytable
where WBS1 Like @WBS1
And DescCategory Like @DescCategory
AND PatIndex('%'+'<!--'+'%',Description)>0

open MyCursor

FETCH NEXT FROM MyCursor into @WBS1, @DescCategory

WHILE @@FETCH_STATUS = 0
	BEGIN

select @ptr= textptr(Description) 
	,@startpos=patindex('%<!--%',Description)
	,@endpos=patindex('%-->%',Description)
	FROM mytable
	where WBS1 Like @WBS1
	And DescCategory Like @DescCategory


set @commentlen=@endpos-@startpos-4
set @adjuststart = @startpos+4-1

UpdateText mytable.Description @ptr @adjuststart @commentlen 'DotNetGnat'



		FETCH NEXT FROM MyCursor into @WBS1, @DescCategory
	END

CLOSE MyCursor
DEALLOCATE MyCursor
Go

-DNG

 

[mrdenny]

That is the same code that I would use.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DotNetGnat]

Thanks for confirming Denny. May be its just that i am dealing with a large table and it is taking the time it is supposed to take...

-DNG

 

[mrdenny]

I would assume. Updating text fields isn't exactly fast.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DotNetGnat]

Denny,What do you think about the following procedure i came up with to replace nonascii characters in the text field...

CREATE PROCEDURE myproc	
        @WBS1 varchar(30),
	@DescCategory varchar(10)

AS

declare @ptr binary(16) ,
	@i int ,
	@datalen int ,
	@DelPos int
	, @Char int

Declare MyCursor CURSOR for
select WBS1, DescCategory from mytable 
where WBS1 Like @WBS1
And DescCategory Like @DescCategory
AND PATINDEX('%[^a-zA-Z0-9]%',Description)<> 0
open MyCursor

FETCH NEXT FROM MyCursor into @WBS1, @DescCategory

WHILE @@FETCH_STATUS = 0
	BEGIN


select @i = 0
	, @datalen = datalength(Description) 
from mytable where 
	DescCategory = @DescCategory
	and WBS1 = @WBS1

while @i < @datalen
begin
	select @DelPos = @i
	select @i = @i + 1
	Select 	@Char = ascii(substring(Description,@i,1))
	from 	mytable
	where 	WBS1 = @WBS1
		and DescCategory = @DescCategory

	if @Char < 32 or @Char = 255
		begin
			select 	@ptr = textptr(Description)
			from mytable where
				DescCategory = @DescCategory
				and WBS1 = @WBS1
			updatetext 
			mytable.Description 
					@ptr 
					@DelPos
					1 
					' '
		end
end
FETCH NEXT FROM MyCursor into @WBS1, @DescCategory
	END

CLOSE MyCursor
DEALLOCATE MyCursor

GO

AS you can see i am going to character by character which is taking lot of time...Do you offer any changes to achieve better perfomance

-DNG

 

[VBrit]

Ok got an idea. Take your string manipulation code, and put it in a user defined function.

Then from your select statement call the user defined function. Now there are no cursors so it should speed up performance considerably.

Also, you may not want to introduce patindex into your where clause - very slow!

Hope this helps