CTE Script Needed for delete statement

Question

CTE Script Needed for delete statement

Diyas

SSCarpal Tunnel

Points: 4851
More actions
June 29, 2016 at 2:05 pm

#327496

Hi,
we are using below script for delete records.but it is taking time
USE AdventureWorks2008R2;
GO
DELETE * FROM Purchasing.PurchaseOrderDetail
WHERE DueDate = '20020701';
GO
is there any possibility of chance to delete the rows from table WHERE DueDate = '20020701'; USING CTE Funcion.
Can we any one provide the script for faster deletes

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

Sree Divya (6/29/2016)
Hi,
we are using below script for delete records.but it is taking time
USE AdventureWorks2008R2;
GO
DELETE * FROM Purchasing.PurchaseOrderDetail
WHERE DueDate = '20020701';
GO
is there any possibility of chance to delete the rows from table WHERE DueDate = '20020701'; USING CTE Funcion.
Can we any one provide the script for faster deletes

A cte will NOT be any faster. How many transactions are there on a single date that you would notice any kind of lag during the delete?

You could try and put the database in simple recovery mode to minimize logging.

ALTER DATABASE database_name SET Recovery SIMPLE

-- Do Deletes

ALTER DATABASE CIS SET Recovery FULL

Or you could delete in batches if you can refine the Where clause.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

LinksUp (6/29/2016)
You could try and put the database in simple recovery mode to minimize logging.
ALTER DATABASE database_name SET Recovery SIMPLE
-- Do Deletes
ALTER DATABASE CIS SET Recovery FULL

Gah!!!

Simple recovery does not minimise delete logging. Deletes are logged exactly the same in all recovery models

What switching to simple will do is break the log chain, impact the ability to restore and cause log backups to fail until a full/diff backup is taken, which, if there's any disaster in that time period, could result in unacceptable data loss. Oh, and break log shipping if there is any, requiring the secondary to be reinitialised.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sree Divya (6/29/2016)
is there any possibility of chance to delete the rows from table WHERE DueDate = '20020701'; USING CTE Funcion.

Yes, but I don't know why you'd want to seeing as it would be executed exactly the same as the current code, same times.

Does that table have an index on the DueDate column?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

GilaMonster (6/29/2016)
Gah!!!

My bad. I mis-interpreted what I had read in the docs. It said something along the lines that transactions are logged but that the space used will be overwritten.

Thanks for the correction.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 5

hi,

thanks for sharing.

already that database in simple recovery model.

but deletes it is more time.

how to resolve this issues very fastly

even inserts dont take this much time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

LinksUp (6/29/2016)
It said something along the lines that transactions are logged but that the space used will be overwritten.

Yup, that's correct. Simple recovery means that the log space is automatically marked as reusable at regular intervals.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 7

thanks

can you please share the code

how to delete by batch wise

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

GilaMonster (6/29/2016)
Does that table have an index on the DueDate column?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LinksUp SSCertifiable Points: 6526 More actions · Answer 9

Sree Divya (6/29/2016)
thanks
can you please share the code
how to delete by batch wise

That is something only you can answer. I have no idea what your table looks like and what columns you have that would help with batches.

A general outline would be something like this:

DELETE FROM Purchasing.PurchaseOrderDetail

WHERE DueDate = '20020701'

AND someOtherCol between 1 and 1000

Then you would increment the range from 1001 to 2000 and so on.

Does your DueDate have a time element? Then you could delete transactions within each hour.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

LinksUp (6/29/2016)
Sree Divya (6/29/2016)
thanks
can you please share the code
how to delete by batch wise
That is something only you can answer. I have no idea what your table looks like and what columns you have that would help with batches.
A general outline would be something like this:
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE DueDate = '20020701'
AND someOtherCol between 1 and 1000
Then you would increment the range from 1001 to 2000 and so on.
Does your DueDate have a time element? Then you could delete transactions within each hour.

Just keep in mind that the point of batching isn't necessarily to delete faster, it's to have less impact on other queries (via locks) and less impact on the transaction log.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LinksUp SSCertifiable Points: 6526 More actions · Answer 11

GilaMonster (6/29/2016)
Just keep in mind that the point of batching isn't necessarily to delete faster, it's to have less impact on other queries (via locks) and less impact on the transaction log.

That I knew. Thanks for the reminder.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

s-sql SSCommitted Points: 1970 More actions · Answer 12

You can do a batch delete of the rows, in the batch increments.

USE AdventureWorks2008R2;

GO

--Delete in batches of 10000 rows

While @@ROWCOUNT <> 0

DELETE Top 10000 * FROM Purchasing.PurchaseOrderDetail

WHERE DueDate = '20020701'

END

GO

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Sree Divya (6/29/2016)
Hi,
we are using below script for delete records.but it is taking time
USE AdventureWorks2008R2;
GO
DELETE * FROM Purchasing.PurchaseOrderDetail
WHERE DueDate = '20020701';
GO
is there any possibility of chance to delete the rows from table WHERE DueDate = '20020701'; USING CTE Funcion.
Can we any one provide the script for faster deletes

It would appear that you're trying to delete legacy data from more than a decade ago. It may be MUCH more effective to copy the data you want to keep to a new table, drop the old table, and rename the new table. Of course, you have to concern yourself with any foreign keys or indexes but it could be comparatively lightning fast.

Remember that DELETEs affect EVERY INDEX and related KEY on the table and, possibly FK's on other tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Y.B. SSChampion Points: 11649 More actions · Answer 14

If you are worried about locking a table when deleting a lot of rows you could delete in batches. As far as actual delete speed just make sure you are effectively using indexes.

SELECT TOP 10000

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num,

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 2 AS ID

INTO #test

FROM

dbo.syscolumns c1

CROSS APPLY dbo.syscolumns c2

WHILE @@rowcount > 0

BEGIN

SELECT COUNT(*) num_of_rows FROM #test --only for demonstration

WAITFOR DELAY '00:00:05' --adjust to what is appropriate

DELETE TOP (4000) FROM #test WHERE ID = 0

END

SELECT * FROM #test

DROP TABLE #test

SELECT quote FROM brain WHERE original = 1
0 rows returned