Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28

    Unanswered: UPDATE freezing problem

    Hi, i am having a problem with UPDATE.

    I have the following table with about 4 million rows:
    CREATE TABLE [newauth] (
    [authnumber] [int] NULL ,
    [batchnumber] [int] NULL ,
    [accountnumber] [varchar] (20) NULL,
    [authcode] [varchar] (10) NULL ,
    [authused] [char] (1) NULL ,
    [loaddate] [int] NULL ,
    [trandate] [int] NULL ,
    [trantime] [int] NULL ,
    [cardtype] [char] (1) NULL ,
    [mcc] [char] (4) NULL ,
    [amount] [int] NULL ,
    [transactionnumber] [int] NULL
    ) ON [PRIMARY]
    GO

    There is an index on the authnumber field. I am calling the following stored procedure to update the transactionnumber.

    CREATE PROCEDURE UpdateAuthWithTrans(@lAuth int, @lTrans int)
    AS
    UPDATE NewAuth
    SET TransactionNumber = @lTrans
    WHERE AuthNumber = @lAuth
    GO

    I need to update about 1 million rows. Approx 50000 calls to this SP work OK. Then the DB freezes. Enterprise mgr shows the following (screen shot attached)

    I cant figure out why this is freezing. I have tried update statistics newauth, dbcc checktable, rebuilding the index, rebooting the server.

    Any ideas would be appreciated, thanks in advance for any help.
    Attached Thumbnails Attached Thumbnails screendump.gif  

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    False alarm,

    I misread the screen. Another user was jumping and locking the table... my mistake (is it Friday yet?)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Fine...but 50,000 sproc calls?

    No thanks...

    Where do you get

    @lAuth int, @lTrans int

    From?

    Another table right?

    If so, you should be performing an update with a join...
    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.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    Normally i would, but there was some screwy business logic behind the scenes that was collecting data from a variety of places and systems.

Posting Permissions

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