updating Text field---very slow

[DotNetGnat]

Guys,

I came up with this below sp to replace nonascii characters in the text field with single space...

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 through character by character in the text field for each record...which is taking lot of time...Do you guys have any suggestions for better performance...

Experts, looking forward to hear your suggestions...

-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

 

[DotNetGnat]

VBrit,

Thanks for your reply...

1. I introduced patindex because that returns only 13K records from the total 65K records...when compared with and without patindex...with patindex yielded good results...

2. can you show me how to put this in UDF and how to call it...may be i am not completely sure of what you are suggesting...

Thanks

-DNG

 

[VBrit]

Hi DotNetGnat,

Had a play, I'm stumped..... ntext is a pain. I'll try again with this later!

Sorry!