TRANSACTION 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 2005 Forums

Transact-SQL (2005)

TRANSACTION Problem

Author

Topic

Sanjayprakash
Starting Member

3 Posts

Posted - 2010-09-10 : 04:54:31

WHILE(1=1)
BEGIN
BEGIN TRANSACTION
SET @SQL = 'DELETE TOP(5000) from eventlog8 where YearID = 2008'
EXEC (@SQL)
SET @Count = @@RowCOUNT
COMMIT TRANSACTION
IF(@Count = 0)
BREAK;
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION

END
END

I run that query to delete record in SQLserver 2005 ISQL window.
After record are been deleted when i try to close the SQL window it ask me if i want to commit the changes. why it not commit the changes when commit is written in that SQL. Whats problem with that code.

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 05:08:06

I dont understand the need for using a loop.
Wont just


DELETE TOP(5000) from eventlog8 where YearID = 2008

be enough?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sanjayprakash
Starting Member

3 Posts

Posted - 2010-09-10 : 05:09:43

I have to delete million of record. I am trying to delete few record at time so it not block the whole system. That is why i am using loop.

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 05:11:54

So I guess

DELETE TOP(your few records) from eventlog8 where YearID = 2008

will work.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sanjayprakash
Starting Member

3 Posts

Posted - 2010-09-10 : 05:14:19

I want to know whats problem with this transaction. WHy its not commited.

Thanks
Sanjay Prakash

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 05:33:41

If you want to follow a totally wrong approach then sorry I dont think anyone here will want to help you.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-09-10 : 06:50:47

No point using dynamic SQL. Can't see ay point using a transaction either, the delete is Atomic (unless the actual logic is more complex?).

Using DELETE TOP n is not very efficient in my experience. The time to find the next "TOP n" is slower than the time to delete - and probably gets worse as more and more data is deleted and index gets fragemented / stats are out of date / etc.

Depends a bit what the critiera are for the WHERE clause of course, but usually they aren't the Clustered Index columns!

We do

Get PK of all rows to be deleted into temporary table; temp table has an IDENTITY column
LOOP while records remain to be processed.
DELETE batch size of rows joining Temp Table to Main Table. Batch size controlled by range on IDENTITY column in Temp Table.
Adjust batch size (**)
Use WAITFOR to pause to allow other processes to run
Force a Tlog backup every N rows-deleted to prevent excessive LDF file growth
END LOOP

(**) we evaluate the elapsed time for the Delete and if it is longer than expected we reduce the batch size by half. If shorter than expected we increase the batch size by 10% (upto a configured Max batch size)

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-10 : 12:00:06

quote:
Originally posted by Idera

If you want to follow a totally wrong approach then sorry I dont think anyone here will want to help you.

That's a perfectly ligitimate Question from the OP!

Maybe the app asks you regardless even when there is nothing to commit.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-10 : 12:42:49

Just to backup and add to what Kristen said.. Why dynamic slq? Also, no need for transactions unless logic is more complicated that you've shown. The error handling you have won't work as expected becasue you are executing other statements between the DELETE and the @@ERROR check.

If you wanted to simplify things you could just do:

DECLARE @Count
SET @Count = 1

WHILE @Count > 0
BEGIN
	DELETE TOP(5000) from eventlog8 where YearID = 2008
	SET @Count = @@ROWCOUNT
END

Kristen
Test

22859 Posts

Posted - 2010-09-10 : 12:49:58

DECLARE @Count int

(unless that is a default? In which case I've learnt something - but I'll refrain from using it! I hate ambiguity in code

)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-10 : 12:53:23

quote:
Originally posted by Kristen

DECLARE @Count int

(unless that is a default? In which case I've learnt something - but I'll refrain from using it! I hate ambiguity in code )

Good question.. :) Not that I know of, just me being a lazy slacker.. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources