deadfully slow cursor?

I've got the following piece of code in a stored procedure, and despite the tables only having about 25K records, its dreadfully slow.

DECLARE Perfer CURSOR FOR
SELECT PerformerID
FROM PAMRA_tbl_navnmatch (NOLOCK)

DECLARE @.test as int

OPEN Perfer

FETCH NEXT FROM Perfer
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Perfer into @.test
UPDATE PAMRA_tbl_navnmatch
SET PAMRA_tbl_navnmatch.Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn) ,
PAMRA_tbl_navnmatch.Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn),
PAMRA_tbl_navnmatch.Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2]),
PAMRA_tbl_navnmatch.Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer),
PAMRA_tbl_navnmatch.Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet),
PAMRA_tbl_navnmatch.Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer),
PAMRA_tbl_navnmatch.Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab),
PAMRA_tbl_navnmatch.IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer]),
PAMRA_tbl_navnmatch.IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle),
PAMRA_tbl_navnmatch.Franavision = 1
FROM PAMRA_tbl_navnmatch INNER JOIN NAMEMATCH_vw_memberdata ON ltrim(rtrim(PAMRA_tbl_navnmatch.Matchfelt)) = ltrim(rtrim(NAMEMATCH_vw_memberdata.[Sgenavn]))
WHERE PAMRA_tbl_navnmatch.PerformerID = @.test
END

CLOSE Perfer
DEALLOCATE Perfer
GO

Is there any way to speed things up? I mean, its been running for more than 45 minutes now. I can track the progress, and it does move forward, BUT yawn its slow.

Its even run on a dual xeon 3.2 server with 4 gigs of memory, only other acticity is a few simple selects on other databases. No locks or anything.

Whats amiss? or is the comparison between char fields just dreadded?HUH?

First, your FETCH Statement doesn't have an into.

Second you don't need the cursor

Third you're already refereincing the table in the update that's in the cursor.

Forth, you're going to update all rows anyway...

Is someone playing a trick on you?|||index on NAMEMATCH_vw_memberdata?

why are you using a cursor for this ? it looks as if you should be able to use an insert...

-Kilka|||why are you using a cursor for this ? it looks as if you should be able to use an insert...

-Kilka

Huh?

This should do the same thing.

UPDATE n
SET Sgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Sgenavn)
, Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsna vn)
, Medlemsnavn2 = convert(char(50),NAMEMATCH_vw_memberdata.[Medlemsnavn 2])
, Medlemsnummer = convert(int, NAMEMATCH_vw_memberdata.Medlemsnummer)
, Nationalitet = convert(char(10), NAMEMATCH_vw_memberdata.Nationalitet)
, Organisationsnummer = convert(char(10),NAMEMATCH_vw_memberdata.Organisat ionsnummer)
, Medlemskab = convert(char(20), NAMEMATCH_vw_memberdata.Medlemsskab)
, IPDnummer = convert(int, NAMEMATCH_vw_memberdata.[IPD Nummer])
, IPDroll = convert(char(20), NAMEMATCH_vw_memberdata.IPDrolle)
, Franavision = 1
FROM PAMRA_tbl_navnmatch n
INNER JOIN NAMEMATCH_vw_memberdata m
ON ltrim(rtrim(n.Matchfelt)) = ltrim(rtrim(m.[Sgenavn]))|||yup :)

do you have better luck that way ?|||the only reason why I do it using a cursor is because the full update simply dies... it takes yonks time...

my first guess was "somethings terribly wrong"... which is true... but since I can't change that the server is slow, I figured doing it cursor-wise, record by record, the update would take time, but in the end complete anyway.

So, yes, something is playing with, the fact that the database is - apparently - mindnumbingly slow for God knows what reason..

/Trin

P.S. I let the clean update run for 3+ hours, then I just gave up... the cursor version takes little under an hour to do.. so although not exactly Einstein, it gets the job done. I was just hoping there was any other way to boost it.|||Apparently the problem is solved.

Somewhere in the scripting of creating tables, I hadn't included indexes... so, at new creation, no indexes were made..

After I added indexes I was able to the basic UPDATE without cursor fairly quickly...

sigh..|||Let's be very clear here.

A Set Update will always out-perform a cursor based solution.

If you are having performance problems, you should trouble shoot that...not through a cursor at it...

And I'm just curious...what's with all the TRIM and CONVERT usage?

Post the DDL of thos 2 tables please.|||Troubleshooting isn't always an option when you have a deadline... the cursor got the job done on time, and now I can troubleshoot while performing the same operations on a different set of data.

The reason for the trims is that much of the populated data is inserted into the tables by various dubious access forms and excel sheets. Spaces in front of and behind stuff... so I merely do it to ensure blanks are killed off until I get to the point of trimming at the front-end.

DDL?