Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: 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.

    Code:
    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.Søgenavn = convert(char(50),NAMEMATCH_vw_memberdata.Søgenavn),
    	PAMRA_tbl_navnmatch.Medlemsnavn = convert(char(50),NAMEMATCH_vw_memberdata.Medlemsnavn),
    	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.Organisationsnummer),
    	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.[Søgenavn]))
    	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?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Kilka101
    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.

    Code:
         UPDATE n 
            SET Søgenavn		= convert(char(50),NAMEMATCH_vw_memberdata.Søgenavn) 
    	  , Medlemsnavn		= convert(char(50),NAMEMATCH_vw_memberdata.Medlemsnavn)
    	  , 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.[Søgenavn]))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    yup

    do you have better luck that way ?
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  6. #6
    Join Date
    Oct 2005
    Posts
    183
    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.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    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..
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2005
    Posts
    183
    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?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •