Table clean up taking too long

Question

I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more than 6 hours. How can I optimize here?

query = """ select grid_id from grid into ##grid_temp
            where 
            ....some logic.....

           set rowcount 10000
               while 1 = 1
               begin delete from grid_info where grid_id in (select grid_id from ##grid_temp)
               if @@rowcount = 0
                   break
           end
           set rowcount 0
         """
db_conn.execute_query(query);

Answer 1

You have a performance problem, so investigate it like a performance problem. Use a methodology like Waits and Queues. This will reveal what is the performance bottleneck, including any blocking that may occur. Likely culprits are blocking (your query is blocked by... you and did not do anything in the past 6 hours), log growth, just poor performance (lack of indexes, including on the ##temp_table).

As a side note the use of SET ROWCOUNT to restrict deletes is deprecated and should be replaced with usage of TOP keyword.

Answer 2

This is what your script does:

1. It reads a huge table and then writes 10M rows. Since you say ....some logic..... reasonable to assume it does not use any indexes. And your tempdb is probably on the same disk as main db, which makes writes slower, because of ongoing select.

2. Then it reads ##grid_temp table of 10M rows Many times: for every loop it checks all 10M against grid_info table, even for those grid_id's that's already beend deleted.

Depending on how heavy SELECT part is, your options my suggestions are:

• split your script in 2 parts: select and delete to see how much time each will take and use it as a benchmark.
• add clustered index on grid_id in ##grid_temp after inserting data: it will help delete run faster and will outweight expense of creating the index.
• when deleting in chunks, delete IDs that have already been processed
• add a column Is_Invalid byte null and create a FILTERED index on it. Update this column to mark row as invalid. Then use this column in Where clause to delete rows. If you have more than 1bn rows consider making this column SPARSE.

I should note that every batch of deletes is a separate transaction, thus it is being logged accordingly. Select number of rows in a batch to be within acceptable rollback time, but less then needed to fill up tranasaction log.

Answer 3

I am running this in a python script.

I ate toast for breakfast. Same relevance.

It is taking more than 6 hours.

Yes.

around 10 million rows

A 10 millon row delete can take a LONG time, depending on the hardware you have. There is a lot to be done. This is one reason eneterprise version of SQL server supports table partitioning - it is faster to take a partition out and drop it.

At the end, you can try deleting in smaller chunks (top 10.000) in a loop. You can get appropriate hardware. You can wait.

All options there are.

Ther are two possible botllenecks:

• Finding the rows to delete.... noonw days how large the table is.
• Performance doing the update.

I regularly play around with multi billion row tables and yes, operations on them take time, or you need REALLY GOOD hardware. No, a Raid of 3-4 slow discs is NOT good. A single developer workstation not at all. A raid of SSD starts being.