CODEWITHSUNDEEP
sqlsql-serverperformancebigdata
Funereal
Funereal

Reputation: 673

SQL - Poor Performance 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'?

Upvotes: 3

Views: 125

Answers (7)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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

Upvotes: 2

Asad
Asad

Reputation: 508

If you have an auto incremented column ID than why you are selecting first row using ID?

DELETE TOP (1) from [Table] order by ID ASC

Upvotes: 0

mxix
mxix

Reputation: 3659

You should create an index on timestamp.

You could setup a filtered index, since it's a fairly big table.

CREATE NONCLUSTERED INDEX IX_Table_timestamp ON Table([timestamp]) WHERE CONVERT(DATE,[timestamp]) = '2014-01-01';

And then delete rows by small batches.

Code from an older post from Aaron Bertrand.

SET NOCOUNT ON;

DECLARE @r INT;

SET @r = 1;

WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (10000) 
    dbo.Table
    WHERE CONVERT(DATE,[timestamp]) = '2014-01-01';

  SET @r = @@ROWCOUNT;

  COMMIT TRANSACTION;

END

Upvotes: 0

Mike Miller
Mike Miller

Reputation: 16575

I'm guess ID is an identity column? probably the lazy DBA has left it as a clustered index too? just do

DELETE TOP (10) from [Table] order by ID ASC

Be warned though in Full logging mode this is going to be intense

Upvotes: 0

sagi
sagi

Reputation: 40491

Why are you using LIKE ? This is most defiently slows down your query, LIKE is usually used to compare partially string.

You can just trunc the date time, and use normal comparison :

SELECT TOP 10 [id]
      ,[clientid]
      ,[type]
      ,[timeStamp]
      ,[message]
FROM [db].[dbo].[table]
WHERE cast(timeStamp AS DATE) = '2014-01-01'

Upvotes: 1

Matt
Matt

Reputation: 15061

Unsure why your using LIKE when there is nothing extra that can be added to the datetime.

Either use =.

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

Or if your trying to get all from that day use the CONVERT function. (I chose 103 as i see your from Spain and use the DD/MM/YYYY format).

SELECT TOP 10 [id]
      ,[clientid]
      ,[type]
      ,[timeStamp]
      ,[message]
FROM [db].[dbo].[table]
WHERE CONVERT(VARCHAR(11),timeStamp ,103) = '01/01/2014'

Upvotes: 0

Backs
Backs

Reputation: 24913

Simple?

WHERE timeStamp = '2014-01-01 00:00:00.000'

Upvotes: 1

Related Questions