2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.