April 13, 2009 at 1:27 pm
For the server what is better to use delete or truncate. I know truncate (deletes data faster) and delete slower, but for the performance what is better?
April 13, 2009 at 1:28 pm
What do you mean by performance? Most of the time, that means speed, and you already said (correctly) that truncate is faster. So, what exactly do you mean?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 13, 2009 at 1:35 pm
There's a lot more to consider than performance.
Truncate deletes all the data in the table. Delete can have a where clause allowing conditional deletes.
Truncate does not fire triggers, delete does.
Truncate is not allowed on a replicated table or one with foreign key constraints, delete is.
Truncate requires ALTER TABLE permissions, delete requires DELETE permissions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2009 at 5:40 pm
yulichka (4/13/2009)
For the server what is better to use delete or truncate. I know truncate (deletes data faster) and delete slower, but for the performance what is better?
:w00t:
With all due respect to previous posters that took your joke seriously... was it a joke, wasn't it?
Just let build a decision table based on your question...
slower => bad performance
faster => good performance
😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 13, 2009 at 7:56 pm
I had a case when We had to clean database nearly 45 GB. It was a vendor's product. Vendor gave a simple delete script to purge/delete unwanted historical data. DBA who was responsible for that product at my company spent hours deleting historical records and finally came to me if there is any way we could speed up this process.
I then analyzed database and found there were 3 tables occupying almost 45 GB Data. I first thought I will truncate data but they had foreign key constraints build on those tables.
I then performed following tasks,
Step 1: Took a full backup.
Step2: Script foreign key constraints on these three tables.
Step3: Drop foreign key constraints on these three tables.
Step4: Truncate 3 tables (took less than a second).
Step5: Build foreign key constraints on those three tables using scripts I generated before dropping constraints.
Whole process took less than a minute...
I don't suggest any one to follow this route, unless they know what they are playing with...
*** Play Safe.***
~ IM.
April 14, 2009 at 3:28 am
I would just add that delete operation is recorded in transaction log for each row but that is not the case with truncate. So while using delete statement it should be considered that it may also result in growth of transactional log file according to number of deleted rows.
DBDigger Microsoft Data Platform Consultancy.
April 14, 2009 at 4:37 am
Moreover, if you really need to delete data based on some criteria you could do it in smaller batches using the ROWCOUNT statement. However, you need to keep in mind all of the above mentioned caveats.
April 14, 2009 at 6:31 am
Thank you everyone for your response, All I am trying to do delete data from the table where date >=30,to schedule a job to run every month. Is my transaction log will grow fast?
April 14, 2009 at 6:38 am
The growth of the log size would depend on how many records are deleted. You can limit the impact on the system and the log file by deleting in batch. You can change the rowcount to whatever number you like. You could also use the if condition to check the time of day and decide whether you want the script to continue to execute or exit. This will help you in making sure the delete process doesn't run in to business hours or any other nightly processing.
SET NOCOUNT ON
SET ROWCOUNT 10000 -- Or some other value
WHILE 1 = 1
BEGIN
DELETE FROM table_name
WHERE dtAccessed >= some_date
IF @@ROWCOUNT = 0 OR check_current_time
BREAK
END
SET ROWCOUNT 0
April 14, 2009 at 6:40 am
yulichka (4/14/2009)
Thank you everyone for your response, All I am trying to do delete data from the table where date >=30,to schedule a job to run every month. Is my transaction log will grow fast?
That depends on how much data you have in the table that needs to be deleted. If it is a lot, there is a way to write the delete routine to complete the data purge in batches so that you may control the growth of the transaction log by taking t-log backups between batches.
We just need to know what your requirements are for the delete, including the volume (number of records) to be deleted monthly.
April 14, 2009 at 7:04 am
It will also depend on what recovery model your database is in. If you're in Simple, then batch-deletes probably won't cause it to grow much. If Full, then backups between batches will keep the transaction log from growing, but you might eat up even more disk space with the backups. (It's usually a bad idea to have a production database in Simple recovery, but sometimes it's necessary. We'd need more data on your situation and set up before being able to judge that point.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2009 at 8:18 am
I run this: and get error:(he DELETE statement conflicted with the REFERENCE constraint "FK_packages_package_lists". The conflict occurred in database "CSWReportDatabase", table "dbo.packages", column 'packagelist_id'.
The statement has been terminated.)
My code:
SET NOCOUNT ON
SET ROWCOUNT 10000 ---
WHILE 1 = 1
BEGIN
DELETE dbo.package_lists
FROM dbo.package_lists INNER JOIN
dbo.packages ON dbo.package_lists.packagelist_id = dbo.packages.packagelist_id
WHERE (dbo.packages.void_flag = 0) AND (DATEDIFF(day, dbo.package_lists.shipdate, GETDATE()) >= 180)
IF @@ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
April 14, 2009 at 8:29 am
yulichka (4/14/2009)
I run this: and get error:(he DELETE statement conflicted with the REFERENCE constraint "FK_packages_package_lists". The conflict occurred in database "CSWReportDatabase", table "dbo.packages", column 'packagelist_id'.The statement has been terminated.)
My code:
SET NOCOUNT ON
SET ROWCOUNT 10000 ---
WHILE 1 = 1
BEGIN
DELETE dbo.package_lists
FROM dbo.package_lists INNER JOIN
dbo.packages ON dbo.package_lists.packagelist_id = dbo.packages.packagelist_id
WHERE (dbo.packages.void_flag = 0) AND (DATEDIFF(day, dbo.package_lists.shipdate, GETDATE()) >= 180)
IF @@ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Looks like you have a foreign key declared. that means you also have to delete the data from the child table as well.
April 14, 2009 at 8:51 am
How can I delet 2 tables together?Thank you
April 14, 2009 at 8:54 am
You can delete the data from the table which has the foreign key or you can disable constraint checks and then delete.
So it really depends on your database design.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply