1

I am doing some testing and have a simple setup. An order table linked to a customer table via a foreign key with On cascade delete. There is a unique nonclustered index on the (customer_id, order_id) columns to support faster cascade deletes.

Below characteristics of the two tables.

Customer table:
1000 records,
Avg_fragmentation: 0,
Clustered index on customer_id (INT)
Clustered Index total page count: 16

Order table:
10 000 records,
Avg_fragmentation: 0,
Clustered index on order_id (INT)
Clustered Index total page count: 38

Deleting 3 customers causes 25 orders to be deleted. From the execution plan, I see that the index on the foreign key is used by the query optimizer. enter image description here Below is the output from STATISTICS IO:

With the index on the foreign key
Table 'order'.     Scan count 3, logical reads 156
Table 'Worktable'. Scan count 2, logical reads  11
Table 'customer'.  Scan count 1, logical reads   8

Without the index on the foreign key
Table 'order'.     Scan count 1, logical reads 138
Table 'Workfile'.  Scan count 0, logical reads 0
Table 'Worktable'. Scan count 2, logical reads 11
Table 'customer'.  Scan count 1, logical reads 8

I wondered why there is so high Scan count and logical reads on the order table just to delete 25 rows even with a foreign key indexed? Without the index on the foreign key logical reads go down to 138 which seems faster although the table is scanned and there is more data to process.

4
  • 1
    Cannot reproduce: I get more reads without a supporting index than with dbfiddle.uk/25M0tWam Commented Mar 2 at 20:29
  • Thank you @Charlieface for taking the time. For deleting a single customer indeed adding an index causes less logical reads. But when you DELETE TOP (3) FROM Customer then logical reads are almost equal in two cases and the scan count rises to 3 for the case when the index is used. If you DELETE TOP (4) FROM Customer or more customers then you will see that having an index causes more logical reads.
    – Backend
    Commented Mar 2 at 23:00
  • Come to think of it: the repro wasn't good as it was comparing using and maintaining the NC index vs no index, we should compare vs not using but still maintaining it. See dbfiddle.uk/ffHYmwXC Scan count will be higher because it's doing seeks and each one counts as a Scan, so not really something to think about. Commented Mar 3 at 0:42
  • Thanks again. I understand your point, but indexing a foreign key is generally regarded as a best practice, so I assumed that in such a basic setup like here, where we have only 2 tables, an additional index would always be faster than no index. Anyway, I found that if you increase the number of customers to 10k or 100k (link ) then adding an index proves to be way faster than no index by a magnitude of 20 or 50 (at least as far as logical reads count goes). So it seems that an index on a foreign key makes your deletes faster unless your table is very small.
    – Backend
    Commented Mar 4 at 1:12

0

Your Answer

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

Browse other questions tagged or ask your own question.