Solved

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

Posted on 2015-01-05
4
152 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:ScottPletcher
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

22 Experts available now in Live!

Get 1:1 Help Now