Solved

How do I Delete millions of data points and Store the deleted records in SQL 2005?

Posted on 2015-01-05
4
155 Views
Last Modified: 2015-01-05
Hi guys,

I am trying to delete approximately 13.5 million data points while simultaneously moving the deleted data to a storage folder.  When we try to look up production trends for the past two weeks the graph takes too long to load the 13.5 million points.  The objective is to keep only the current data for the past fourteen days in the original table while storing the rest in old records.  I have made a query to delete up to fourteen days ago by removing 100,000 rows at a time and would like to stick to a similar number because it allows me to see the progress of the query and does not overload the query.  

Here is part of the table I am using:
id                source_id         datetime                  data
11720123      27      9/29/2014 12:33:31 PM      422
11720124      17      9/29/2014 12:33:31 PM      1969
11720125      25      9/29/2014 12:33:31 PM      771
11720126      16      9/29/2014 12:33:31 PM      518
11720127      6      9/29/2014 12:33:31 PM      1079
11720128      12      9/29/2014 12:33:31 PM      854

Here is the query I have used to delete the data thus far:
USE [master]

DECLARE @total_rows_in_table int
DECLARE @rows_deleted int
DECLARE @total_rows_deleted int
SELECT @total_rows_in_table = COUNT(*)
FROM dbo.table1 WITH (NOLOCK)
SET @rows_deleted = 0
SET @total_rows_deleted = 0

WHILE 1 = 1
BEGIN
      DELETE top (100000)
      FROM dbo.table1
      WHERE Datetime<= DATEADD(d, -14, Getdate())      
      IF @rows_deleted=0
BEGIN
            CHECKPOINT
      END --IF
      WAITFOR DELAY '00:00:00.15'
END --WHILE

PRINT 'Total Rows Deleted=' + CAST (@total_rows_deleted AS varchar (10))
0
Comment
Question by:Tom_Hickerson
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40531358
Why don't you do it the other way round? Instead of deleting, then copying, why not copy, then delete? Of the top of my head, something like:

INSERT INTO dbo.table2
SELECT top (100000) *
FROM dbo.table1
WHERE Datetime<= DATEADD(d, -14, Getdate()) 

DELETE dbo.table1
FROM dbo.table1 T1
JOIN dbo.table2 T2
ON T1.id = t2.id

Open in new window

0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40531376
You might also want to take a look at the OUTPUT clause:

http://msdn.microsoft.com/en-us/library/ms177564.aspx
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40531770
Is the table clustered on datetime?  You should delete using the clustering key only.

Why count the records in the table first?  That's a waste of time, since you're ultimately deleting solely by datetime and not by record count.

You need a condition in the DELETE to prevent you from deleting the last 14 days' worth.

Also, when a DELETE finds no rows to delete, you should BREAK out of the loop.  Btw, you test variable @rows_deleted but you don't have a statement:
SET @row_deleted = @@ROWCOUNT
after the delete, so the variable will always contain NULL.
0
 
LVL 1

Author Comment

by:Tom_Hickerson
ID: 40531867
The row count was left over from a query I previously used on another set of tables and was not intended for this query.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 42
migration MS SQL database to Oracle 30 59
Count with a subquery showing details 10 40
Insert query into temp tables using Coldfusion 3 12
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now