1

I have a table featureData in Access that has ~1.8 million rows. I have another table idNumbers that has ~7000 rows. I want to delete records from featureData using the following query:

delete from featuredata where stationId in (select id from previousStationId)

This query is very slow (I started running it an hour ago and the progress bar is still less than halfway complete). Note that stationId is an indexed field, but it's slow even when I change its index setting to "No". Is there way I can speed this up?

sigil
  • 9,370
  • 40
  • 119
  • 199
  • How many rows are you deleting aproximatly? – litelite Jun 29 '17 at 19:58
  • 1
    Do not use `IN`! Rather then it use `Exists`. Or... use `INNER JOIN`. See: [How to delete in MS Access when using JOIN's?](https://stackoverflow.com/questions/5585732/how-to-delete-in-ms-access-when-using-joins) – Maciej Los Jun 29 '17 at 20:00
  • @MaciejLos `Where Exists` is proceeding at the same rate as `In`. – sigil Jun 29 '17 at 20:23
  • 2
    Is id also indexed? – elc Jun 29 '17 at 20:39
  • Definitely make sure that `stationId` and `id` are indexed – Brad Jun 29 '17 at 22:26
  • Do this with a join rather than an IN. Access may be running "select id from previousStationId" on every record. A join would only do this once. Even at 2M records a delete on an indexed column should happen within minutes. – strattonn Jun 30 '17 at 03:16

0 Answers0