I need help in speeding up the execution's time of my stored proc

Question

Hello guys.I wanna ask some little question.How can i speed up the perfomance of the next stored proc.First,a need to say that there are 30 000 000 rows in the next table:

CREATE TABLE tblT
  ( 
   [RowID] [int] IDENTITY(1, 1)
                 NOT NULL
  ,[CheckDateID] int NOT NULL
  ,[SSN] varchar(10)
  ,[CheckDate] datetime
  ,[val] money
    CONSTRAINT [PK_tblT]
    PRIMARY KEY CLUSTERED ( [CheckDateID] ASC, [RowID] ASC )
  )

And here we are,this is a SP and i need some reasonable advices on how i'll be able to modify this proc.Critical here is a Time of proc's execution...

DECLARE @RowMin [int]
 ,@RowMax [int]
 ,@Portion [int]
 ,@CurMin [int]
 ,@CurMax [int]
 ,@strMessage varchar(100)


SET @RowMin = ( SELECT min (RowID) FROM tblT )
SET @RowMax = ( SELECT max (RowID) FROM tblT )
SET @Portion = 350000
SET @CurMin = @RowMin
SET @CurMax = @CurMin + @Portion

WHILE @CurMax <> @CurMin
BEGIN

    Update A
    set val = val*8,3, --???
    from tblT  A 
    where A.RowID Between  @CurMin and @CurMax

    SET @CurMin = @CurMax
    SET @CurMax = case WHEN ( @CurMax + @Portion ) < @RowMax
                    THEN @CurMax + @Portion
                    ELSE @RowMax
         END

END

Answer 1

Some thoughts:

• Is there an index on RowID?

• Why not do

  SELECT @RowMin=min(RowID),@RowMax=max(RowId) FROM tbLT

instead of two separate queries

• Why not simply

  update tbLT SET val=val*8.3 WHERE rowID BETWEEN @CurMin and @CurMax

• And the biggest question, why are you chunking the update statement? It seems to me you are trying to do

update tbLT SET val=val*8.3

Do you need to break the update statement into smaller pieces for contention reason? What is the recovery model on this database, you could be getting a performance hit from SQL logging this update operation...

Answer 2

Besides what Sparky said - why did you choose to put the PK on (CheckDateId,RowID) ?? Any particular reason?

By doing this, you basically made this index unusable for queries (like your UPDATE statement) which reference RowID alone --> you'll end up having full table scans instead.

Three options:

1. Put your PK on (RowID) alone
2. Change your PK to be on (RowID, CheckDateID) - yes, order does matter in this case!
3. Add a new non-clustered index on (RowID) alone

All three options are designed to speed up the range query on the RowID that you have in your UPDATE statement.

Be forewarned, however - tweaking your database structure to speed this one query up could potentially have side-effects on the rest of your system and your other queries, and you could potentially slow those down. Tweaking just a single query / stored proc in isolation is always a very dangerous operation - you could wreck a lot of havoc on the rest of the system.