4

I must choose between three possible DELETE methods in SQL Server. The table: RawData stays at around 500GB.

Here is the table:

CREATE TABLE dbo.rawData ( 
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT, 
    MeasureId INT, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    DateEnergy DATETIME DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION 
    )
GO


CONSTRAINT [PK_RawData] PRIMARY KEY CLUSTERED ([AreaId] ASC, [MeasureId] ASC, [DateEnergy] ASC)

CREATE INDEX IX_RawData_AreaId
  ON RawData (AreaId);
GO

CREATE NONCLUSTERED INDEX [IX_RawData_DateEnergy]
ON [dbo].[RawData] ([DateEnergy])
INCLUDE ([AreaId],[MeasureId])

GO
CREATE NONCLUSTERED INDEX [IX_RawData_MeasureId_DateEnergy]
ON [dbo].[RawData] ([MeasureId],[DateEnergy])
INCLUDE ([AreaId])

Method 1: Add a column IsDeleted type boolean.

  • Would require that this column be indexed as well (exclude rows deleted by the SELECT)
  • Update this boolean column (update = rowlock)?

Method 2: Add a new table that will store rows that need to be deleted

  • Outer join between the two tables for the SELECT
  • No update necessary on RawData

Method 3: Use an existing column to detect rows to be deleted

  • Update the AreaID to a negative value to mark it for deletion (update = rowlock)?
  • Use the existing suppression system that removes rows by batches of 10000

Constraints:

The deletion might need to be cancelled at any given time

Questions:

  1. Which method would be the most efficent?

  2. Are there any other methods not mentionned that might be better?

Update July 11th 2019

The number of rows being deleted depends on their collection date. So there could be millions or hundreds or none.

  • 4
    The INCLUDE parts in your non-clustered indexes are redundant. The columns of the CI are always included in an NCI index. – ypercubeᵀᴹ Apr 3 '14 at 8:28
  • 1
    We have a similar requirement. The parallel would be a date column showing when the row was deleted and then after a certain amount of time as such the row is moved to a different table by an overnight process. – Paul Apr 3 '14 at 10:15
  • 2
    Being serious, here is a link to MSDN: Clustered and Nonclustered Indexes Described, where it states "The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. " – ypercubeᵀᴹ Apr 3 '14 at 14:28
  • 2
    How many of how many rows do you want to delete? – miracle173 May 3 '14 at 10:40
  • 1
    You don't even say how many rows you want to delete... I can't see how @miracle173's comment didn't result in this question being deleted. – Evan Carroll Jul 10 '19 at 21:58
4

The best solution to this problem is nearly always method 2, but with two tables UNION ALL instead of LEFT JOIN together (one to hold the deleted, one for the active). There are several reasons why this is superior:

  1. You can maintain separate statistics on active and deleted rows. Assumption: you will have more deleted than active over time. This means that the "active rows" table stays small and doing fullscans is easier. It also makes things like index rebuilds (if you do them) faster on the active rows.
  2. Statistics of old rows will not affect news rows. This helps you avoid skew problems.
  3. Each table can live on it's own filegroup. This means you can move deleted rows off to cheaper storage.
  4. You can have different indexing/partitioning strategies on the deleted rows vs. the active ones. For example, you may choose to use a column store index on the old rows if they are read via a scan often, but changed very rarely.
  5. The deleted table can be taken offline with a table SWITCH (for maintenance) without disrupting the system too much.

The two table solution can be implemented with a DELETE instead of trigger that moves the rows to deleted instead of deleting them.

The only downside of solution 2 is that you will need to modify your queries to distinguish between deleted and not deleted rows and tables. This can be done via a view, but it is safer to avoid this if possible. The view can confuse the optimiser and there are cases where using a view instead of a table gives you horrible execution plans. If you do use a view, you should add an `IsDeleted' column to both tables and put a check constraint on it. This ensure that the optimiser will not try to seek BOTH table for every query (which would double your IOPS).

For completeness, and to answer your last question, there are some other ways to "solve" this:

Method 4: Add an IsDeleted column and partition on this column. Drawback: Does not allow separate statistics on the two partitions (statistics are table, not partition based, in SQL Server)

Method 5: Use a filtered index to apply different indexing (and secondary storage) on deleted and active rows. Drawback: Good luck getting the optimiser to behave properly with this method.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.