0

I have a table with different articles. The article has two states:

  • new
  • enabled

The user can only order articles, who are enabled. The user also can delete new articles. And here is the problem, it tooks too long the delete a 'new' article. It's about 10 minutes. The reason is, there are some foreign keys from other tables. There are about 10million orders, 15million bills and 5million returns.

I already created different indexes on the order, bill and return table. I also tried to disable all constraints, delete the article and enable the constraints again. If the constraints are disabled, it works really fast, but it took about 10 minutes the enable my constraints again.

Does anyone has an idea how I can solve this problem? The solution lives only two years and the tables will keep growing for the next few years, so I need a solution, who keeps working for a longer time. My best idea was to create a 'deleted' flag on the article table, but in that case, my article table will grow faster.

I could post an execution plan, but it will only say, that it took too long the check the order, bill and return table.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
wydy
  • 71
  • 12
  • 1
    You can create this flag for deletion, estimate when your server is not under pressure (midnight) and delete them then. I dont know how much an index will help you on a very "accessed" table and if that will actually help you with your performance because every time the table changes the index rebuilds itself. – SubqueryCrunch Feb 05 '15 at 08:02
  • 1
    Another approach will be to create this business rule that articles can be deleted only the first 30 minutes after posting them. And the first 30 minutes they will remain in a special table which has no constraints maybe and have a job that transfers the older than 30 minutes to your main articles table. This approach is a bit tricky because you might violate constraints when trying to move them from your temp articles table to the main one if you dont have good error handling which can replace the constraints for the temp table. – SubqueryCrunch Feb 05 '15 at 08:16

1 Answers1

1

The indexes on the foreign key columns in the related tables should do the trick:

CREATE INDEX ArticleID ON Orders (ArticleID)
CREATE INDEX ArticleID ON Bills (ArticleID)
CREATE INDEX ArticleID ON Returns (ArticleID)

If you already have these indexes but it still doesn't work, look at the execution plan and check if there is an index scan or an index seek in the related tables. If there is a scan, it means that there is no suitable index in that table. Post the execution plan if you are still having problems.

Razvan Socol
  • 5,426
  • 2
  • 20
  • 32