如何在从数据库删除实体时提高性能？ [英] How to improve performance when deleting entities from database?

问题描述

我使用SQL Server 2008为我的DAL启动了Entity Framework 4的ASP.NET项目。在我的数据库中，我有一个表 Users （例如5.000.000）。

最初，我的用户表设计如下：

  id uniqueidentifier 
名称nvarchar（128）
密码nvarchar（128）
电子邮件nv​​archar（128）
 Role_Id int 
 Status_Id int 
  

我修改了表格，并添加了 MarkedForDeletion column：

  Id uniqueidentifier 
名称nvarchar（128）
密码nvarchar（128）
电子邮件nv​​archar（128）
 Role_Id int 
 Status_Id int 
 MarkedForDeletion位
  

每次应删除每个实体，或使用 MarkedForDeletion 属性。这意味着我需要更新的值，并在某个时候删除所有用户设置为true的存储过程或类似的值。

MarkedForDeletion 属性的更新不会与删除操作相同吗？

解决方案

根据系统的需求/需求/未来需求，考虑将已删除实体移到新表中。设置一个'audit'表来保存那些被删除的。考虑有人想要恢复的情况。

对于性能问题：更新是否与删除相同？不。更新将是一个更轻的操作，特别是如果你有一个索引PK（errrr，这是一个guid，而不是一个int）。重点是对位字段的更新要便宜得多。 （质量）删除将强制数据的重新洗牌。也许这个工作属于宕机或低批量期间。

关于性能：基准测试看看会发生什么！给定您的表有500万行，很高兴看到您的SQL Server在这两种情况下的索引，分页等的当前状态下执行。对数据库进行备份，并还原到新的数据库中。在这里你可以根据自己的喜好选择沙箱。运行&时间的情况：

• 批量删除


• 更新 c


• 移至审计表

/ ul>



对于图书，请尝试：






• 此答案：书


• < a href =http://stackoverflow.com/questions/343709/book-recommendation-for-advanced-sql-design-scripting-and-optimiziation/343725#343725>对Adam Mechanic的书的建议


• 关于数据库书籍的另一个问题。





I started an ASP.NET project with Entity Framework 4 for my DAL, using SQL Server 2008. In my database, I have a table Users that should have many rows (5.000.000 for example).

Initially I had my Users table designed like this:

Id  uniqueidentifier    
Name    nvarchar(128)   
Password    nvarchar(128)
Email   nvarchar(128)
Role_Id int 
Status_Id   int

I've modified my table, and added a MarkedForDeletion column:

Id  uniqueidentifier    
Name    nvarchar(128)   
Password    nvarchar(128)
Email   nvarchar(128)
Role_Id int 
Status_Id   int 
MarkedForDeletion   bit

Should I delete every entity each time, or use the MarkedForDeletion attribute. This means that I need to update the value and at some moment in time to delete all users with the value set to true with a stored procedure or something similar.

Wouldn't the update of the MarkedForDeletion attribute cost the same as a delete operation?

解决方案

Depending on the requirements/needs/future needs of your system, consider moving your 'deleted' entities over to a new table. Setup an 'audit' table to hold those that are deleted. Consider the case where someone wants something 'restored'.

To your question on performance: would the update be the same cost as a delete? No. The update would be a much lighter operation, especially if you had an index on the PK (errrr, that's a guid, not an int). The point being that an update to a bit field is much less expensive. A (mass) delete would force a reshuffle of the data. Perhaps that job belongs during a downtime or a low-volume period.

Regarding performance: benchmark it to see what happens! Given your table with 5 million rows, it'd be nice to see how your SQL Server performs, in its current state of indexes, paging, etc, with both scenarios. Make a backup of your database, and restore into a new database. Here you can sandbox as you like. Run & time the scenarios:

• mass delete vs.
• update a bit or smalldatetime field vs.
• move to an audit table

In terms of books, try:

• this answer re: books
• a recommendation for Adam Mechanic's book
• another question on database books.

这篇关于如何在从数据库删除实体时提高性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！