Geek Answers Handbook

Delete data from a large table

I have a table with about 10 fields for storing gps information for clients. Over time, as we added more customers, this table grew to about 14 million rows. As gps data arrives at the service, it constantly inserts a row into the table. 90% of the data is not overloaded, that is, the customer does not care where the vehicle was 3 months ago, but the most recent data is used to create tracking reports. My goal is to write sql to perform data wiping older than a month.

Here is my problem: I can’t use TRUNCATE TABLE because I’ll lose everything? Yesterday I wrote a delete table statement with a where clause. When I ran it on a test system, it closed my table and the gps simulation inserts were interrupted. Also my transaction log grew to 6 GB as it tried to register every delete.

My first thought was to delete the data a little at a time, starting with the oldest, but I was wondering if there is a better way.

+5
source share
15 answers

try it

WHILE EXISTS (SELECT * FROM table WHERE (condition for deletion))

START
SET ROWCOUNT 1000
DELETE Table WHERE (condition for deletion)
SET ROWCOUNT 0

end

1000

+4

2 :

SQL 2005 , , .

, dba, "", , , .

+10

, . .

Oracle (SQL Server )

create table keep as select * from source where data_is_good = 1;
truncate table source;
insert into source select * from keep;

, .

Oracle , . SQL- , "keep" "source",

+4

SQL Server 2005 2008, - - - . .

+3

, ?

, 6 .

+2

/ ( , , ). , , , , , t .

DELETE FROM TABLENAME 
WHERE datediff(day,tableDateTime,getdate() > 90

, .

+2

. .

  • , .

  • .

"ETL" .

. - - - . , , .

+2

, , , . , GPS, . , . , . , , , .

, . .

+1

, ( GPS ).

(, 10%) , .

? , , .

+1

, , . , - , , . Perl script, 1000 . , (, , , - ), , .

PseudoSQL:    max (primId) < 3    , primId < maxPrimId limit 1000

, : , , . , . , .

0

oracle, . , ... .

- ..

0

delete - ? , , , delete.

, , . , , .

0

- . . Script Management Studio. Script , . table2. , , , 2. , , tableOld. 2 . . , 2. . 1) , , . Script, . 2) , _ ​​ .

0
source

I came up with the following T-SQL script that gets an arbitrary amount of recent data.

IF EXISTS(SELECT name FROM sys.tables WHERE name = 'tmp_xxx_tblGPSVehicleInfoLog')
BEGIN
    PRINT 'Dropping temp table tmp_xxx_tblGPSVehicleInfoLog'
    DROP TABLE tmp_xxx_tblGPSVehicleInfoLog
END
GO

PRINT 'Creating temp table tmp_xxx_tblGPSVehicleInfoLog'
CREATE TABLE [dbo].[tmp_xxx_tblGPSVehicleInfoLog](
    [GPSVehicleInfoLogId] [uniqueidentifier] NOT NULL,
    [GPSVehicleInfoId] [uniqueidentifier] NULL,
    [Longitude] [float] NULL,
    [Latitude] [float] NULL,
    [GroundSpeed] [float] NULL,
    [Altitude] [float] NULL,
    [Heading] [float] NULL,
    [GPSDeviceTimeStamp] [datetime] NULL,
    [Milliseconds] [float] NULL,
    [DistanceNext] [float] NULL,
    [UpdateDate] [datetime] NULL,
    [Stopped] [nvarchar](1) NULL,
    [StopTime] [datetime] NULL,
    [StartTime] [datetime] NULL,
    [TimeStopped] [nvarchar](100) NULL
) ON [PRIMARY]
GO

PRINT 'Inserting data from tblGPSVehicleInfoLog to tmp_xxx_tblGPSVehicleInfoLog'
SELECT * INTO tmp_xxx_tblGPSVehicleInfoLog 
FROM tblGPSVehicleInfoLog 
WHERE tblGPSVehicleInfoLog.UpdateDate between '03/30/2009 23:59:59' and '05/19/2009  00:00:00'
GO

PRINT 'Truncating table tblGPSVehicleInfoLog'
TRUNCATE TABLE tblGPSVehicleInfoLog
GO

PRINT 'Inserting data from tmp_xxx_tblGPSVehicleInfoLog to tblGPSVehicleInfoLog'
INSERT INTO tblGPSVehicleInfoLog 
SELECT * FROM tmp_xxx_tblGPSVehicleInfoLog 
GO
0
source

To prevent the transaction log from getting out of control, modify it as follows:

DECLARE @i INT
SET @i = 1
SET ROWCOUNT 10000

WHILE @i > 0
BEGIN
    BEGIN TRAN
        DELETE TOP 1000 FROM dbo.SuperBigTable
        WHERE RowDate < '2009-01-01'
    COMMIT
    SELECT @i = @@ROWCOUNT
END
SET ROWCOUNT 0

And here is the version using the preferred TOP syntax for SQL 2005 and 2008:

DECLARE @i INT
SET @i = 1

WHILE @i > 0
BEGIN
    BEGIN TRAN
        DELETE TOP 1000 FROM dbo.SuperBigTable
        WHERE RowDate < '2009-01-01'
    COMMIT
    SELECT @i = @@ROWCOUNT
END
0
source

More articles:


All Articles