正在努力开发一种新的 post 方法。我发现我在瓶颈foreach语句上遇到了非常长的等待时间。我将 5 到 8k(取决于发送的项目)保存到数据库中。该方法成功，但通常需要整整 60 秒以上。我一直在研究如何实际执行异步方法，但我不确定这是否真的会解决问题。
这是完整的方法
[HttpPost]
public ActionResult ConfirmSend(int? SystemGeneralAnnouncementId) {
var systemGeneralAnnouncement = (SystemGeneralAnnouncementId == null) ? null : _uow.SystemGeneralAnnouncementRepository.GetById(SystemGeneralAnnouncementId.Value);
List<Status> status = new List<Status>();
if (systemGeneralAnnouncement.Statuses.Length > 0)
{
status.AddRange(systemGeneralAnnouncement.Statuses.Split(',').Select(item => (Status) Enum.Parse(typeof (Status), item)));
}
var allEmailAddresses = new List<PointOfContact>();
var EmailAddresses = new List<PointOfContact>();
var result = new List<PointOfContact>();
foreach (var item in status)
{
result = _uow.PointOfContactRepository.GetAllByStatus(item).ToList();
allEmailAddresses.AddRange(result);
}
if (systemGeneralAnnouncement.SendToRecipients.Contains("(1) All Three Contact Types"))
{
mailAddresses = allEmailAddresses;
}
else
{
if (systemGeneralAnnouncement.SendToRecipients.Contains("(2) All Contacts "))
{
EmailAddresses.AddRange(allEmailAddresses.Where(r => r.PointOfContactType == PointOfContactTypes.Primary).ToList());
}
if (systemGeneralAnnouncement.SendToRecipients.Contains("(3) All Compliance contacts"))
{
pocEmailAddresses.AddRange(allEmailAddresses.Where(r => r.PointOfContactType == PointOfContactTypes.Secondary).ToList());
}
if (systemGeneralAnnouncement.SendToRecipients.Contains("(4) All Authorities"))
{
pocEmailAddresses.AddRange(allEmailAddresses.Where(r => r.PointOfContactType == PointOfContactTypes.SigningAuthority).ToList());
}
if (systemGeneralAnnouncement.SendToRecipients.Contains("(5) All Rate Contacts"))
{
EmailAddresses.AddRange(allEmailAddresses.Where(r => r.PointOfContactType == PointOfContactTypes.TuitionRates).ToList());
}
if (systemGeneralAnnouncement.SendToRecipients.Contains("(6) Specified Email Address"))
{
var pocs = new List<PointOfContact>();
string[] emails = systemGeneralAnnouncement.EmailAddresses.Split(',');
foreach (string email in emails)
{
var addContact = new InstitutionPointOfContact { Email = email };
User user = _uow.UserRepository.GetByEmail(email);
if (user == null)
{
addContact.FirstName = "Not Created Account Yet";
}
else
{
addContact.FirstName = user.FirstName;
addContact.LastName = user.LastName;
}
List<PointOfContact> idAssociatedToUser =
_uow.PointOfContactRepository
.GetAllByEmail(email)
.ToList();
if (idAssociatedToUser.Count == 0)
{
addContact.IDNumber = "N/A";
}
else
{
string[] opeidArray = opeidAssociatedToUser
.Select(x => x.OPEIDNumber)
.ToArray();
addContact.OPEIDNumber = string.Join(",", opeidArray);
}
Contacts.Add(addContact);
}
EmailAddresses.AddRange(Contacts);
}
}
具体Foreach
if (EmailAddresses.Count > 0)
{
foreach (PointOfContact emailAddress in EmailAddresses.Where(x => x.Email != "").ToList())
{
string firstName = emailAddress.FirstName == null ? "" : emailAddress.FirstName.Trim();
string lastName = emailAddress.LastName == null ? "" : emailAddress.LastName.Trim();
string userName = firstName + " " + lastName;
string emailBody = WebUtility.HtmlDecode(systemGeneralAnnouncement.EmailBody);
SaveToDatabase(emailAddress.Email, emailBody, systemGeneralAnnouncement.Subject, UserIdentityHelper.GetUserEmailAddress + " (" + UserIdentityHelper.GetUserId + ")", systemGeneralAnnouncement.SystemGeneralAnnouncementId, userName, emailAddress.OPEIDNumber);
LogInstitutionEmail(systemGeneralAnnouncement.Subject, emailBody, emailAddress.Email, emailAddress.OPEIDNumber, systemGeneralAnnouncement.EmailAttachmentLocation);
}
}
return View("GeneralAnnouncementGeneratedConfirmation");
}
和数据库方法：
private void LogInstitutionEmail(string subject, string emailBody, string email, string opeidNumber, string emailAttachment)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("Insert Into InstitutionEmails (Since, Subject, Email, EmailAddress, OpeidNumber, FirstReadDateTime, Attachment) VALUES(@Since, @Subject, @Email, @EmailAddress, @idNumber, NULL, @Attachment)", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Since", Value = DateTime.Now });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Subject", Value = subject });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Email", Value = emailBody });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmailAddress", Value = email });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@idNumber", Value = idNumber });
if (!string.IsNullOrEmpty(emailAttachment))
{
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Attachment", Value = emailAttachment });
}
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
}
}
private void SaveToDatabase(string emailRecipient, string emailBody, string subject, string userWhoSentIt, int systemGeneralAnnouncementId, string userName, string opeidNumber)
{
try
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterContext"].ConnectionString))
{
conn.Open();
var cmd = new SqlCommand("Insert Into EmailQueue (EmailRecipients, EmailBody, EmailSubject, UserWhoSentIt, QueueDate, SystemGeneralAnnouncementId, UserName, idNumber) VALUES(@EmailRecipients, @EmailBody, @EmailSubject, @UserWhoSentIt, @QueueDate, @SystemGeneralAnnouncementId, @UserName, @idNumber)", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter() {ParameterName = "@EmailRecipients", Value = emailRecipient });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmailBody", Value = emailBody });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@EmailSubject", Value = subject });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@UserWhoSentIt", Value = userWhoSentIt });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@QueueDate", Value = DateTime.Now });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@SystemGeneralAnnouncementId", Value = systemGeneralAnnouncementId });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@UserName", Value = userName });
cmd.Parameters.Add(new SqlParameter() { ParameterName = "@idNumber", Value = idNumber });
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
}
}
我的问题是双重的。首先，异步是这种类型的代码(使用 Sql server 2012)的可行选择，正如我在研究中注意到的那样，如果您有数据库级别的瓶颈，异步可能会导致死锁。其次，如果 Async/await 是可行的，那么实际实现它的最佳方式是什么？
更新：post方法仅检索系统常规公告的ID，以及启动该方法的确认。所有实际执行都在方法本身内处理。
更新 2：为了澄清起见，这些项目被传递给稍后触发的任务计划程序，并获取队列中的所有存储项目，然后在后台交付它们。这就是我使用 SQL 调用的原因。
如果您有数据库级别的瓶颈，异步可能会导致死锁
一点也不。
但是，async不会帮助您跑得更快- 这是一个常见的误解。
您要做的是批量更新。