使用MVC 4和Entity Framework 6访问大型数据库的最佳方法是什么 [英] What is the best method to access a large database using MVC 4 and Entity Framework 6

问题描述

我有一个用MVC 4和Entity Framework 6编写的电子邮件营销Web应用程序。该应用程序在Web服务器场的sql数据库中存储了超过1万个电子邮件地址以及联系信息。

I have an email marketing web application written in MVC 4 and Entity Framework 6. The application stores more than 10K email addresses along with contact information in a sql database on a web farm.

尽管数据库很小，只有几百条记录，但是一切都很好。但是，当我完全填充数据库时，使用 Foreach语句检索和写入每个单独的记录会很痛苦。对于某些操作，最多可能需要14分钟才能完成。我曾试图尽量减少每次使用的记录数量，但这会使应用程序变得笨拙。

Everything works fine while the database is small, a few hundred records. However, when I fully populate the database, it is painfully slow retrieving and writing each individual record using "Foreach" statements. For some operations it can take up to 14 mins to complete. I have tried to minimize the number of records I work with at any one time, but it make the application clumsy.

我想知道，还有其他方法可以使用吗？加快速度。也许是SQL存储过程，或者是GitHub上的一些存储过程将使之更加容易。如果有，请告诉我在哪里可以了解更多有关它的信息。任何帮助，将不胜感激。这是查询之一。

I was wondering, are there other methods I could use to speed this up. Perhaps SQL stored procedures, or something on GitHub that would make it easier. If there are, please let me know where I can learn more about it. Any help would be appreciated. Here is one of the queries.

private int AddOrUpdateCampaigns(Campaign campaign
        , IEnumerable<AssignEmailContacts> assignEmailContacts)
{
    DataBaseContext db = new DataBaseContext();
    int TotalAssigned = 0;
    if (assignEmailContacts == null) return(TotalAssigned);

    if (campaign.CampaignId != 0)
    {   
      foreach (var emailContact 
                in assignEmailContacts.Where(a => a.Assigned == false))
      {   
        campaign.EmailContacts.Remove(
                db.EmailContacts.Find(emailContact.EmailContactId));
      }
      foreach (var emailContact 
                in assignEmailContacts.Where(a => a.Assigned))
      {                            
        campaign.EmailContacts.Add(
                db.EmailContacts.Find(emailContact.EmailContactId));
            TotalAssigned += 1;
      }
    }
    return (TotalAssigned);
}

推荐答案

在EF中更新多个数据库行速度慢！

Updating multiple database rows in EF is slow!

我假设有一个名为CampaignContacts的表，其中包含Campaign和Contacts的n对n关系中的数据。幸运的是， EF6 允许您执行原始查询。

I assume there is a table called CampaignContacts containing the data from the n-to-n relationship of Campaign and Contacts. Luckily EF6 allows you to execute raw queries.

using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Text;

public class YourDbContext : DbContext
{
    public void DeleteBatchCampaignContacts(IList<int> ids)
    {
        if (ids == null) return;
        if (ids.Count == 0) return;         

        //ONLY because the parameter comes from a list of Int, else you might risk injection
        Database.ExecuteSqlCommand(string.Format("DELETE FROM CampainContacts WHERE CampaignId in ({0})", string.Join(",", ids)));
    }

    public void UpdateBatchCampaignContacts(int campaignId, IList<int> ids)
    {
        if (ids == null) return;
        if (ids.Count == 0) return;

        Database.ExecuteSqlCommand(string.Format("UPDATE CampaignContacts SET CampaignId = @campaignId WHERE EmailContactId in ({0})", string.Join(",", ids),
            new SqlParameter("@campaignId", campaignId)));
    }
}

注意代码重复。您可以重构这些功能，但这比当前的解决方案要快。您可以添加诸如以50个ID的批次执行查询的功能，以处理数千个ID的更新。这是一个开始。

Notice the code duplication. You can refactor these functions, but this would be quicker than your current solution. You can add features like executing the query in batches of 50 IDs in order to handle thousands of IDs updates. This is a start.

这篇关于使用MVC 4和Entity Framework 6访问大型数据库的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！