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.
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.
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);
}
Updating multiple database rows in EF is slow!
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)));
}
}
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments