I want to delete data from a table that contains millions of rows.

As a test I tried to delete 1 row but it takes over 2mins.

    FROM [geneva.reports.model].PriceDiffs
    WHERE UserID = 384166

There is no index on UserID but there are 3 non-clustered indexes. I tries disabling them but they failed due to lock timeout.

The actual query plan is uploaded to Paste the Plan.

  • 1
    Is there any reason for not having an index on UserID? I think it's the only way your WHERE clause can be processed quickly. – joanolo Mar 5 '17 at 23:12
  • Where is this 'CASE WHEN NOT [Expr1009] IS NULL THEN (0) ELSE NULL END"> <IF> – McNets Mar 5 '17 at 23:14
  • 1
    @McNets Not sure but probably a foreign key or some other kind of constraint – Joe Obbish Mar 5 '17 at 23:17

I can think of two possible explanations for why you experience poor performance with that query.

The first relates to blocking. Have you checked the wait events while the query is running? If it is blocked by another session and waiting for a lock then you don't have a performance problem. Instead, you have a concurrency problem.

The second relates to the table and index structure on the table that you are deleting from. The PriceDiffs table has 194807000 rows. If you don't have an index that can seek on the UserID column it could take a long time to find the single row to delete. There's also a scan on the PriceDifferenceRecords table which I assume is there to enforce a constraint or a foreign key, but that table only has 15843 rows.

You might be wondering why the query takes two minutes to run when it has a relatively low estimated subtree cost of 0.0569578. This is probably due to the row goal introduced to the query by your use of TOP 1. SQL Server scans the IX_PriceDiffs_PriceDate index to find a matching row but the filter is implemented as predicate instead of a seek predicate. I assume this is because UserID is not a key column. Thus, SQL Server scans the index in order until it finds a row where UserID = 384166. If you are really lucky the first matching row could be in the first page of the index. If you are really unlucky the first matching row could be in the last page of the index, so your DELETE query would scan the entire table to delete just one row.

It's worth noting that deleting just one row from a table often isn't a good way to test performance when you need to delete lots of rows. For a starting point, consider creating an index on UserId and deleting in batches.

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.