Stored Procedure is too slow

[cricketer1]

I have the following update statement which is basically updating few fields in one table with another table. This runs fine as a statement on my test DB server and even on the live Db server, but when I put it on a live DB server as a stored procedure, it dies out on me. What could be going wrong. The table being updated is used in Order Level transaction, so there are other updates gong on, could there be a locking issue.

UPDATE AL_INVENTORY
SET AL_STATUS = STATUS,
AL_AVAILABLE_QTY = INVENTORY,
AL_AVAILABLE_DATE = AVAILABLE_DATE
FROM tbl_Inventory_Temp T
WHERE T.PROD_ID = PRODUCT_ID
AND T.CHOICEVALUE_COLOR_ID = AL_COLOR_CHOICE
AND T.CHOICEVALUE_SIZE_ID = AL_SIZE_CHOICE

Please advice how best to tackle such a situation.

Thanks

 

[tlbroadbent]

What follows applies to SQL 7 and 2000. If you are using an earlier version, some will not be applicable.

Locking can be an issue when performance is slow. You can check for locking using Enterprise manager. You can also use the SPs sp_who2 and sp_lock. There is also a modified version of sp_lock available at SWYNK.com that gives a more detailed view of locks.

http://www.swynk.com/friends/achigrik/LockView.asp

You can use the Estimated Execution plan to evaluate the SP and see where bottlenecks may exist. You can also Analyze the indexes with Index Tuning Wizard.

Check for more performance tuning recommendations at

http://www.sql-server-performance.com/default.asp

Good luck. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin