Wierd update problem? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wierd update problem?

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-20 : 16:36:18

This takes 20 seconds when I execute both at once.
update tablea set a_yn=1
IF (@@ROWCOUNT <> 0)
BEGIN
delete tablea where a_yn=1
END

This takes 1 second when I execute one at a time.
update tablea set a_yn=1
GO
IF (@@ROWCOUNT <> 0)
BEGIN
delete tablea where a_yn=1
END
GO

I need to execute both at once -- since they will be part of a stored procedure. I need it to be 1 second. Any ideas why executing both at once takes longer?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-02-21 : 00:28:52

if you're going to delete the whole table why not truncate it, it doesn't log so it's super fast.

TRUNCATE TABLE tablea

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-21 : 17:31:50

Have a look at the query plans and maybe specify an index.
It's worthwhile bouncing the server before the test to clear out old plans.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources