k0rs k0rs - 26 days ago 7
SQL Question

SQL - Low Perfomance SELECT Query on 377 million table

I got a table with the following structure:

Id | clientid | type | timeStamp | message |


I'm using this query to get the first rows of table to start deleting rows but is crashing the DB:

SELECT TOP 10 [id]
,[clientid]
,[type]
,[timeStamp]
,[message]
FROM [db].[dbo].[table]
WHERE timeStamp LIKE '%2014-01-01 00:00:00.000%'


Is there any way to get the first rows without crashing and delete them before arrive to
timeStamp
'2016-01-01 00:00:00.000'
?

Answer Source

I'm not sure if I understand this correctly:

If you set an index on your TimeStamp column it should be absolutely fast to filter rows greater or smaller a given date.

These lines will delete everything from your table where the TimeStamp is smaller than 2016-01-01. Only current entries will remain...

Attention: Be careful! Don't test against real data! :-)

DELETE FROM myTable
WHERE ID IN(SELECT ID 
            FROM myTable AS innerTbl 
            WHERE innerTbl.Timestamp<{ts'2016-01-01 00:00:00'}
           )

Update

This will delete 1000 rows per call. The number behind "GO" will execute this snippet 377000 times. Test with smaller numbers...

BEGIN TRANSACTION;
DELETE FROM myTable
WHERE ID IN(SELECT TOP 1000
            ID 
            FROM myTable AS innerTbl 
            WHERE innerTbl.Timestamp<{ts'2016-01-01 00:00:00'}
           );
COMMIT;
GO 377000