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
            ....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
           set rowcount 0
  • Any reason you use a subselect / in clause instead of the simpler join? And I do not see a primary key difinition. Wny a temp table not an easier table variable? – TomTom May 28 '13 at 7:18
  • And why select * ... into if you only need the grid_id? – Remus Rusanu May 28 '13 at 7:21
  • not using "select *" using grid_id only – Dumper May 28 '13 at 7:31
  • "....some logic....." refers to WHERE clause or there are other DML statements? – bojan May 28 '13 at 12:08
  • I meant to say: If you are going to use a TABLE VARIABLE, make sure to add a primary key to the grid_id column. That is effectively the only index you can add to a TABLE VARIABLE. Also make sure there is a supporting index on the grid_info table. That was a lot harder to write than it should have been! (4 edits) – datagod May 28 '13 at 14:30

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.


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.

  • SPARSE index? Did you mean Filtered index? – Remus Rusanu May 28 '13 at 10:34
  • Actulally better would be to kill the temp table, use a table variable and then delete the processed items from that variable. – TomTom May 28 '13 at 10:36
  • @RemusRusanu, Yes, Filtered Index and Sparse column. Thanks – Stoleg May 28 '13 at 10:49

I am running this in a python script.

I ate toast for breakfast. Same relevance.

It is taking more than 6 hours.


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.

  • OP is deleting in chunks of 10000 – Remus Rusanu May 28 '13 at 7:16
  • But possibly in one transaction - not in a transaction each. That also points to item one - finding the rows to delete. If that is a HUGH table, finding the 10.000 rows each run can take a long time. – TomTom May 28 '13 at 7:17
  • Agreed, could be one single transaction, but is not clear from the script posted. – Remus Rusanu May 28 '13 at 7:18

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.