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 |
|
rdaunce
Starting Member
7 Posts |
 Posted - 2005-09-30 : 14:37:38
|
| I have a stored procedure with the following form:ALTER PROCEDURE [dbo].sproc @ID int = null ASIf @ID Is Not NullBegin -- update the hitcounter for this update table set counter = counter + 1 where ID = @ID SELECT [ID], [TypeID], [UserID], [Name], [Data], [Counter] FROM [dbo].[table] WHERE [ID] = @IDEndElseBegin SELECT [ID], [TypeID], [UserID], [Name], [Data], [Counter] FROM [dbo].[table]EndGOThis procedure has worked great for quite a long time. Today it started to take a long time to execute this procedure (15+ seconds). The only thing that has changed in this database is the actual data in the tables.I was able to stop the slowdown by commenting out the UPDATE statement in the procedure. Once that was removed it started running great again. Does anyone have any idea why this might have started happening? Any suggestions on how to fix it or modify it so this doesn't occur? Thanks.--Bob |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-09-30 : 15:46:49
|
| Is ID indexed?Could be you need to update statistics or the update was being blocked.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Select and Update