5

I have a table with millions of rows. I'm deleting where year(data) in (2013,2014,2015). It's taking more than 4hrs. I'm trying now to delete only year 2013 but it's still very very slow (every year has like more than 600.000 rows and it has a varbinary column to store .PDF files).

I know that indexes can decrease performance on deletes. It will be a good Idea to Disable both indexes in that table, and then re-enable them?

enter image description here

Can it be faster if I create a view, and delete from the view?


Edit:

I made this query to delete it by top 1:

delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
while @@rowcount > 0
    begin
    delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
    end
10
  • If these indexes are heavily used by your applications then they will become slow. Also if the indexes are there for 'enforcing' uniqueness then you risk that you cannot enable them afterwards.
    – Marco
    Mar 22, 2017 at 15:01
  • Oh, This database is the development database. But for sure I would like to know how to proceed in a production environment.
    – Racer SQL
    Mar 22, 2017 at 15:02
  • how big are the pdf's? Keep in mind every row deleted gets written to the log file. Also, is it a problem for this delete process to take a while? You could do it in batches, and just let it run.
    – Hannah Vernon
    Mar 22, 2017 at 15:03
  • 1
    Try to create a procedure that loops and delete the rows in small chunks. Per month or week. You need to commit after each chunk.
    – Marco
    Mar 22, 2017 at 15:07
  • 6
    Creating an index may actually help you here... if there isn't one on (data). Also rewrite the query to "where data >= '20130101' and data < '20160101'" , which will use an index on (data) if there is one. "Where year(data) in (2013, 2014, 2015)" won't. SQL may be spending (wasting) a lot of time just finding the row it needs to delete. Also think about larger batches than 1 row at a time sqlperformance.com/2013/03/io-subsystem/chunk-deletes Mar 22, 2017 at 16:20

3 Answers 3

5

Depending on how interconnected this table is with others in your database, and assuming that the table only has data for 2013-present, the fastest solution might be:

  1. Script out your original table, and create a new empty table with the same structure.
  2. INSERT the data for 2016 and 2017 into the new table.
  3. DROP the original table.
  4. Rename the new table to the original table's name (see the docs for ALTER TABLE).

Deletes are generally enough slower than inserts that it's probably faster to copy out 25-30% of the records in the table than to delete 70-75% of them. However, of course, you need to have sufficient disk space to hold the duplicates of the data to be kept to be able to use this solution (as noted by Toby in the comments).

If you do this, you'll want to be absolutely certain that the new table ends up exactly like the original, including any indexes, triggers, etc. You might want to truncate the original, rename it, and keep it around for a while instead of deleting it, just to be sure there's nothing you've missed. Also, outside of any clustered index, you may want to add clustered indexes and triggers after you've inserted the 2016 and 2017 data. If triggers are involved at all, make sure that whatever you do leaves the rest of your data in a valid state.

If other tables reference your table in foreign key relationships (as suggested by Joe Obbish in the comments), then this becomes somewhat more complicated. I would recommend scripting out all the foreign keys that point to this table, removing them, and then recreating them after the new table has been renamed. See this link to an article by Aaron Bertrand for help with this.

2
  • You could use SELECT * INTO New_MyTable FROM MyTable WHERE data >= '2016-01-01'; DROP TABLE MyTable and then rename the New_MyTable to MyTable
    – Toby
    Mar 22, 2017 at 23:08
  • 1
    @Toby - SELECT ... INTO wouldn't necessarily capture all aspects of the original table. Indexes, triggers - none of that would come over.
    – RDFozz
    Mar 22, 2017 at 23:49
2

As your trying to delete all data between 01/01/2013 00:00:00 and 31/12/2015 23:59:59, try this

delete * from MyTable where data >= '2013-01-01' and data < '2016-01-01' 

That will allow it to use any available index.

2
  • Agreed. I'm pretty sure running YEAR() or any other function on the data column in the WHERE clause will cause it to ignore the index on that column.
    – SQLDevDBA
    Mar 22, 2017 at 22:48
  • Yep I did the year() and it took almost 5hrs. I'm trying now with >=. I will post results
    – Racer SQL
    Mar 23, 2017 at 12:05
1

I highly recommend partitioning your table now.

I'd create a new table partitioned by year that looks just like your current table then insert into it. Going forward, you can more easily prune your table.

Here are several links to a good partitioning discussions... http://www.databasejournal.com/features/mssql/partitioning-in-sql-server-part-1.html https://www.datavail.com/blog/four-things-remember-planning-sql-partitioning/

I'm not a SQL Server guy - I saw an MS SQL Server 2016 documentation suggests there are some differences in 2014 features - but I can't find 2014 documentation.

Here is a link to MS SQL Server 2008... https://msdn.microsoft.com/en-us/library/ms188706(v=sql.105).aspx

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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