Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
izaltsman
A custom title
1139 Posts |
 Posted - 2001-10-12 : 11:05:15
|
Hi, all!I have a stored procedure that I am trying to optimize. The stored procedure carries out a bunch of updates on different tables. All updates are very similar: they replace an old user (@p_loser_user_id) with a new user (@p_winner_user_id)in the owner, creator, and lastmod fields. Here is a sample update statement for one of the T_TASK table: UPDATE tsk SET tsk.task_owner_id = CASE tsk.task_owner_id WHEN @p_loser_user_id THEN @p_winner_user_id ELSE tsk.task_owner_id END, tsk.task_creator_id = CASE tsk.task_creator_id WHEN @p_loser_user_id THEN @p_winner_user_id ELSE tsk.task_creator_id END, tsk.task_lastmod_id = CASE tsk.task_lastmod_id WHEN @p_loser_user_id THEN @p_winner_user_id ELSE tsk.task_lastmod_id END FROM T_TASK AS tsk INNER JOIN T_USER AS u ON (tsk.task_owner_id = u.user_id) WHERE u.comp_id = @p_comp_id AND (tsk.task_owner_id = @p_loser_user_id OR tsk.task_creator_id = @p_loser_user_id OR tsk.task_lastmod_id = @p_loser_user_id) There are indexes on T_TASK.task_owner_id (clustered), T_USER.user_id (non-clustered), and T_USER.comp_id (clustered). If I run the update above all by itself (outside of the stored procedure), the query optimizer gives it an excellentexecution plan (seek on all of those indexes), and the update goes fast. However, when this very SAME update is run in the stored procedure with several dozen similar updates, it gets a crappyexecution plan (scan instead of a seek on task_owner_id_idx). Update takes twice as long. Same thing happens with other updates in this proc. Does anyone know a way to force an index seek in this situation? I tried using FROM T_TASK AS tsk WITH (INDEX(task_owner_id_idx))...but that didn't help. I can not add any new indexes at the moment. I suppose I could break up this one long stored proc into separate ones for each update, but I am not too keen on the idea of having lots and lots of stored procedures just to carry out this one task. |
|
|
|
|
|
|
|
Is there a way to force a seek?