UPDATE in a Stored Procedure

Question

UPDATE in a Stored Procedure

mallannna

SSC Rookie

Points: 47
More actions
July 26, 2006 at 12:03 pm

#67607

Hi All,
Hope some one can help me with the performance problem am facing when
using an UPDATE in a Stored Procedure.
I have an update statement like " UPDATE TABLEA SET COLUMNA =
TABLEB.COLUMNB
FROM TABLEEA , TABLE B
WHERE TABLEB.COLUMNC=TABLEA.COLUMNC
AND TABLEA.DATE_VAL BETWEEN START_DATE AND END_DATE"
If I run this update statement by providing START_DATE AND END_DATE
values , the update takes place in a few seconds for about 100,000
records. I need to run this update statement for 120 times for as many
months. There are 5 update statements like this.
I created a stored procedure wherein I pass a parameter ( e.g,
200401) to retrieve the start(2004-01-01) & end (2004-01-31) dates
from another table and pass these values for the start and end dates
variables in the update statements.
The problem is when I run this update statement from within a
stored procedure it takes more than 10 minutes and with all the 5
update statements in the stored procedure its about an hour plus per
month.
Why is it that it takes such a long time when it only takes a few
seconds when I run the update statement independently.
Appreciate your help in solving this performance issue.
Thanks & Rgds,
Mallik

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MattieNH SSCertifiable Points: 7463 More actions · Answer 1

Mallik,

I've never run into this particular problem, but in the past when questions like this have been posted, responders suggested a phenomenon called 'parameter sniffing'. Search this site using those key words, and you'll find a lot of posts on this.

Mattie

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

check the query plans for the proc and qa query to see if they are different.

I've encountered this type of problem and the solutions can be a bit strange!! so please check the plans first.

try your proc with the values hardcoded as a test and see if it's the same. I assume your QA query uses hard coded values, try using variables.

Let me know how you get on.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

David Korb SSCrazy Points: 2505 More actions · Answer 3

I have found performance gains by selecting a subset of interest into a temp table and updating off of that. This way, you separate the comparison from the update.

select T.ID

into #tempA

from TableA as T

where T.date between @start_date and @end_date

update A

set A.value = B.value

from TableA as A

join #tempA as T on T.id = A.id

join TableB as B on B.id = A.id

asiaindian SSCrazy Points: 2493 More actions · Answer 4

Did you try using > and < instead of BETWEEN for the date range?

mallannna SSC Rookie Points: 47 More actions · Answer 5

hi all,

Thanks for all the responses. I figured out how to solve this problem.

BY altering the update statement in the stored procedure to forcibly use

the index, as

"UPDATE TABLEA SET COLUMNA =

TABLEB.COLUMNB

FROM TABLEA (index DATE_VAL_Index) , TABLE B

WHERE TABLEB.COLUMNC=TABLEA.COLUMNC

AND TABLEA.DATE_VAL BETWEEN @START_DATE AND @END_DATE"

made a huge difference. Now the five updates finish in less than a

couple of minutes against and hour and plus.

Rgds,

Mallik

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

yup, that works and tells me something else is amiss , usually ( and i say this in a general sense ) if you have to force an index there is something wrong with the query or indexes on the tables(s).

This is usually my starting point for investigation - however if it's a secondary index you're forcing they can be fickle!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/