asked on

Table has BigInt timestamp - need to delete rows older than 7 days

I need to write a SP to delete rows that have a timestamp 7 days or older from this table..

--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LiveCoinAllOrderBookBids]') AND type in (N'U'))
--DROP TABLE [dbo].[LiveCoinAllOrderBookBids]
--GO
--IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'LiveCoinAllOrderBookBids')
--BEGIN
--CREATE TABLE [dbo].[LiveCoinAllOrderBookBids](
--	[LiveCoinAllOrderBookBidsID] [int] IDENTITY(1,1) NOT NULL,
--	[LiveCoinAllOrderBookID] [int] NOT NULL,
--	[BidQuantity] DECIMAL(18,8) NOT NULL,
--	[Bid] DECIMAL(18,8) NOT NULL,
--	[TimeStamp] BIGINT NOT NULL,			 
--	CONSTRAINT [PK_LiveCoinAllOrderBookBids] PRIMARY KEY CLUSTERED 
--	(
--		[LiveCoinAllOrderBookBidsID] ASC
--	))	
--END
--GO

Open in new window

Jim Horn

Since there's an identity field, let's assume for the moment that rows are entered with a current Timestamp value.

>I need to write a SP to delete rows that have a timestamp 7 days or older from this table..
Then it's as simple as (try both of these out and see which one is faster)

-- 1
Declare @id int
SELECT @id = MAX(id) FROM LiveCoinAllOrderBookBids WHERE Timestamp < DATEADD(day, -7, GETDATE())
DELETE FROM LiveCoinAllOrderBookBids WHERE id >= @id

-- 2
DELETE FROM LiveCoinAllOrderBookBids
WHERE Timestamp >= DATEADD(day, -7, GETDATE())

Open in new window

Scott Pletcher

You cannot rely on an identity, because any value can be inserted at any time.

If you lookup in this table often by timestamp, you should cluster the table on timestamp, not on identity. The biggest and most damaging, to performance, myth in table design is that "every tables should be clustered by an identity column." FALSE, FALSE, FALSE.

CAMPzxzxDeathzxzx

ASKER

Hey Scott - glad to see that you are still around!

I must be missing something here because the TimeStamp is a BigInt --- [TimeStamp] BIGINT NOT NULL

I think there has to be some kind of convert right?

DELETE FROM LiveCoinAllOrderBookBids
WHERE Timestamp >= DATEADD(day, -7, GETDATE())

Scott Pletcher

Thanks!

Select a few rows and see what the format is. Presumably it's long like YYYYMMDDHHMMSS. Once we see what the pattern is, it's easy enough to gen a bigint to match that pattern.

CAMPzxzxDeathzxzx

ASKER

It does come from a C# Long type.
example = 1519142686097

CAMPzxzxDeathzxzx

ASKER

I just looked at the code and found it is C# Long to BigInt before it is sp'ed to the db into the BigInt column. I'll post a new question regarding the best way to handle these dates. I'm modeling against APIs that kick out UNIX UTC.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CAMPzxzxDeathzxzx

ASKER

You rock!