如何改进MVC中Linq-to-sql的动作方法
本文关键字：方法 Linq-to-sql 何改进 MVC | 更新日期: 2023-09-27 17:53:03
我正在研究一种生成pdf的方法。为此，我使用了tuespechkin库。转换本身非常快。大多数情况下，它需要从数据库中检索数据并创建pdf的html表示。为了提高性能，我尝试使用异步方法和PLinq，但它没有多大帮助。有没有办法优化这个方法?
最昂贵的代码部分(已更新):
StringBuilder html = new StringBuilder(1024 * 1024); // adapt memory reservation depending on document size
//---------------------------- Start page -----------------------------------------------------------//
imageArray = System.IO.File.ReadAllBytes(HttpContext.Server.MapPath("../Content/images/pdf_logo.jpg"));
base64ImageRepresentation = Convert.ToBase64String(imageArray);
html.Append("<div class='break' style='margin: 0 0 0 0px;'>");
html.Append("<img class='report-img' style='margin:150px 0 400px 0;' src='data:image/jpeg;base64," + base64ImageRepresentation + "'></img>");
html.Append("<p class='purpleText' style='color:#8E0033; font-size: 30px; margin:0; line-height:20px;'>" + title + "</p>" +
"<p class='purpleText' style='color:#8E0033; font-size: 16px;'>" + bigTitle + "</p>");
html.Append("<p style='color:#980000; font-size: 50px; margin: 46px 0 0 0;'>" + custName.firstName + " <span>" + custName.lastName + "</span></p>");
html.Append("</div>");
//------------------------------------ 2nd page -----------------------------------------------------------//
html.Append("<div class='text-block break'></div>");
//=======================================================================================================================//
//------------------------------------- Generate menu (p4) ----------------------------------------------------------//
html.Append(header);
html.Append("<span class='menu-title'>INNHOLDSFORTEGNELSE</span>");
html.Append("<ul class='content-list break'>");
//------------------ Innlednings menu --------------------------//
if (await db.Innlednings.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-header'>Innledning</li>");
}
//------------------ Fordelings menu --------------------------//
if (await db.Fordelings.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-header'>Fordeling av bruk og regninger</li>");
}
//--------------------------------- Report Area Bruk Menu ----------------------------------------------------//
//--------------------------- Get transaction category id of selected area ---------------------------//
const string tranCatBruk = "Bruk";
var tranCatIdBruk = 0;
if (await db.TransactionCategories.AnyAsync(n => n.Name == tranCatBruk))
{
tranCatIdBruk = (await (db.TransactionCategories.FirstAsync(n => n.Name == tranCatBruk))).Id;
}
else
{
throw new HttpException(404, "'" + tranCatBruk + "' transaction category does not exist!");
}
if (await db.ReportViews.AnyAsync(n => n.CustomerId == customerId && n.TransactionCategoryId == tranCatIdBruk))
{
html.Append("<li class='list-header'>Brukskonto</li>");
if (await db.FordelingBruks.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Fordeling av brukskonto</li>");
}
var rvBrukItems = await
db.ReportViews.Where(n => n.CustomerId == customerId && n.TransactionCategoryId == tranCatIdBruk).ToListAsync();
foreach (var rvItem in rvBrukItems)
{
var mainCatName = (await db.Categories.FirstAsync(n => n.Id == rvItem.MainCategoryId)).Name;
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.DifferentStoresImage.IsNullOrWhiteSpace() ||
!rvItem.SubCategoryImage.IsNullOrWhiteSpace() ||
!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level2'>" + mainCatName + "</li>");
}
//----------------- get images --------------------------//
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Totalt pr. måned</li>");
}
if (!rvItem.DifferentStoresImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Fordeling</li>");
}
if (!rvItem.SubCategoryImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Kategorier</li>");
}
if (!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Transaksjoner pr. måned</li>");
}
if (!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Gjennomsnittlig handlebeløp</li>");
}
}
}
//--------------------------------- Report Area Regning Menu ----------------------------------------------------//
//--------------------------- Get transaction category id of selected area ---------------------------//
const string tranCatRegning = "Regning";
var tranCatIdRegning = 0;
if (await db.TransactionCategories.AnyAsync(n => n.Name == tranCatRegning))
{
tranCatIdRegning = (await db.TransactionCategories.FirstAsync(n => n.Name == tranCatRegning)).Id;
}
else
{
throw new HttpException(404, "'" + tranCatRegning + "' transaction category does not exist!");
}
if (await db.ReportViews.AnyAsync(n => n.CustomerId == customerId && n.TransactionCategoryId == tranCatIdRegning))
{
html.Append("<li class='list-header'>Regningskonto</li>");
if (await db.FordelingBruks.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Fordeling av regningskonto</li>");
}
var rvBrukItems = await
db.ReportViews.Where(n => n.CustomerId == customerId && n.TransactionCategoryId == tranCatIdRegning).ToListAsync();
foreach (var rvItem in rvBrukItems)
{
var mainCatName = (await db.Categories.FirstAsync(n => n.Id == rvItem.MainCategoryId)).Name;
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.DifferentStoresImage.IsNullOrWhiteSpace() ||
!rvItem.SubCategoryImage.IsNullOrWhiteSpace() ||
!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level2'>" + mainCatName + "</li>");
}
//----------------- get images --------------------------//
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Totalt pr. måned</li>");
}
if (!rvItem.DifferentStoresImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Fordeling</li>");
}
if (!rvItem.SubCategoryImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Kategorier</li>");
}
if (!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Transaksjoner pr. måned</li>");
}
if (!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level3'>Gjennomsnittlig handlebeløp</li>");
}
}
}
//------------------ Inntekt menu --------------------------//
html.Append("<li class='list-header'>Inntekt</li>");
if (await db.InntektPerMonths.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Inntekt per måned</li>");
}
if (await db.InntektSubCategories.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Fordeling av inntekt</li>");
}
if (await db.InntektFordelings.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Fordeling mellom inntekt og forbruk</li>");
}
//----------------- SPARING menu ------------------------//
if (db.SparingKommentarers.Any(n => n.CustomerId == customerId) ||
db.SparingTabells.Any(n => n.CustomerId == customerId))
{
html.Append("<li class='list-header'>Sparing</li>");
if (await db.SparingKommentarers.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Kommentar</li>");
}
if (await db.SparingRads.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Sparetips</li>");
}
if (await db.SparingTabells.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Tabell</li>");
}
}
//----------------- BUDSJETT menu ------------------------//
if (db.Budsjettkommentarers.Any() ||
db.BudsjettGraf1s.Any() ||
db.TotalBruks.Any() ||
db.TotalRegningers.Any())
{
html.Append("<li class='list-header'>Budsjett</li>");
if (await db.Budsjettkommentarers.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Kommentar</li>");
}
if (await db.TotalBruks.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Totalt bruk</li>");
}
if (await db.TotalRegningers.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Total regninger</li>");
}
if (await db.BudsjettGraf1s.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-level2'>Budsjett tall</li>");
}
}
//----------------- OPPSUMMERING(Articles) menu ------------------------//
if (await db.Articles.AnyAsync(n => n.CustomerId == customerId))
{
html.Append("<li class='list-header'>Oppsummering</li>");
}
//------------------ End of Menu -----------------------------------------------//
html.Append("</ul>");
//------------------ two blank pages (6-7) ---------------------------------------//
////------------------------------- Start generating inner pdf ---------------------------------------------//
////---------------------------------------Innledning------------------------------------------------------//
var customerforInnlending = await db.Innlednings.FirstOrDefaultAsync(inl => inl.CustomerId == customerId);
html.Append(header);
string textforInnlending = (customerforInnlending != null) ? customerforInnlending.Text : "<b>Empty Text</b>";
html.Append("<h1 class='menu-title'>Innledning </h1>" +
"<div class='text-block'>" + textforInnlending + "</div>" +
"<p class='break'></p>");
html.Append("<br>");
//-----------------------------------Fordeling-----------------------------------------------------------//
var fordelingList = await db.Fordelings.Where(cus => cus.CustomerId == customerId).ToListAsync();
foreach (var item in fordelingList)
{
if (!item.TransactionsImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling av bruk og regninger </h1>");
html.Append("<img class='report-img circlediagram' width='800px' height='auto' src='" + item.TransactionsImage + "'></img>");
html.Append("<div class='text-block'>" + item.Text + "</div>");
}
if (!item.TopTransactionsImage.IsNullOrWhiteSpace())
{
html.Append("<img class='report-img stolpeddiagram mtop30' width='600px' height='auto' src='" + item.TopTransactionsImage + "'></img>");
}
html.Append("<div class='text-block break'></div>");
}
//---------------------------- Bruskonto title -----------------------------------------------------------//
imageArray = System.IO.File.ReadAllBytes(HttpContext.Server.MapPath("../Content/images/bruk_header.png"));
base64ImageRepresentation = Convert.ToBase64String(imageArray);
html.Append(header);
html.Append("<div class='break' style='display:block;padding-top:300px;padding-left:0px;'>");
html.Append("<img class='report-img' src='data:image/png;base64," + base64ImageRepresentation + "'></img>");
html.Append("</div>");
////------------------------------------------------Fordeling av Bruk------------------------------------------------------//
var fordelingBruk = await db.FordelingBruks.Where(cus => cus.CustomerId == customerId).ToListAsync();
foreach (var item in fordelingBruk)
{
if (!item.MainCatImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1>Fordeling av Brukskonto </h1>");
html.Append("<img class='report-img circlediagram' src='" + item.MainCatImage + "'></img>");
html.Append("<div class='text-block'>" + item.Text + "</div>");
}
if (!item.TopMainCatImage.IsNullOrWhiteSpace())
{
html.Append("<img class='report-img stolpeddiagram mtop30' width='600px' height='auto' src='" + item.TopMainCatImage + "'></img>");
}
html.Append("<div class='text-block break'></div>");
}
//------------------------------------------------ ReportView Bruk------------------------------------------------------//
//--------------------------- Get transaction category id of selected area ---------------------------//
var tranCat = "Bruk";
var tranCatId = 0;
if (await db.TransactionCategories.AnyAsync(n => n.Name == tranCat))
{
tranCatId = (await db.TransactionCategories.FirstAsync(n => n.Name == tranCat)).Id;
}
else
{
throw new HttpException(404, "'" + tranCat + "' transaction category does not exist!");
}
var reportViewBrukList = db.ReportViews.AsParallel().Where(n => n.TransactionCategoryId == tranCatId && n.CustomerId == customerId).ToList();
foreach (var rvItem in reportViewBrukList)
{
var mainCatName = (await db.Categories.FirstAsync(n => n.Parent == null && n.Id == rvItem.MainCategoryId)).Name;
//----------------- get images --------------------------//
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Diverse " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.TotalPerMonthImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.TotalPerMonthText + "</div>");
}
if (!rvItem.DifferentStoresImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling av " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.DifferentStoresImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.DifferentStoresText + "</div>");
}
if (!rvItem.SubCategoryImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling på " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.SubCategoryImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.SubCategoryText + "</div>");
}
if (!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1>" + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.TransactionsPerMonthImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.TransactionsPerMonthText + "</div>");
}
if (!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Antall transaksjoner pr. mnd på " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.AverageTradeValueImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.AverageTradeValueText + "</div>");
}
}
//---------------------------- Regningskonto title -----------------------------------------------------------//
imageArray = System.IO.File.ReadAllBytes(HttpContext.Server.MapPath("../Content/images/regninger_header.png"));
base64ImageRepresentation = Convert.ToBase64String(imageArray);
html.Append(header);
html.Append("<div class='break' style='display:block;padding-top:300px;padding-left:0px;'>");
html.Append("<img class='report-img' src='data:image/png;base64," + base64ImageRepresentation + "'></img>");
html.Append("</div>");
//-------------------------------------------- Fordeling av regninger (page 39) ---------------------------------------------------------//
var fordelingRegninger = await db.FordelingRegningers.Where(cus => cus.CustomerId == customerId).ToListAsync();
foreach (var item in fordelingRegninger)
{
if (!item.MainCatImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1>Fordeling av regninger </p>");
html.Append("<img class='report-img circlediagram' width='800px' height='auto' src='" + item.MainCatImage + "'></img>");
html.Append("<div class='text-block'>" + item.Text + "</div>");
}
if (!item.TopMainCatImage.IsNullOrWhiteSpace())
{
html.Append("<img class='report-img stolpeddiagram mtop30' width='600px' height='auto' src='" + item.TopMainCatImage + "'></img>");
}
html.Append("<div class='text-block break'></div>");
}
//------------------------------------------------ ReportView Regninger (p38-48)------------------------------------------------------//
//--------------------------- Get transaction category id of selected area ---------------------------//
tranCat = "Regning";
tranCatId = 0;
if (await db.TransactionCategories.AnyAsync(n => n.Name == tranCat))
{
tranCatId = (await db.TransactionCategories.FirstAsync(n => n.Name == tranCat)).Id;
}
else
{
throw new HttpException(404, "'" + tranCat + "' transaction category does not exist!");
}
var reportViewRegningerList = db.ReportViews.AsParallel().Where(n => n.TransactionCategoryId == tranCatId && n.CustomerId == customerId).ToList();
foreach (var rvItem in reportViewRegningerList)
{
var mainCatName = (await db.Categories.FirstAsync(n => n.Parent == null && n.Id == rvItem.MainCategoryId)).Name;
//----------------- get images --------------------------//
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Diverse " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.TotalPerMonthImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.TotalPerMonthText + "</div>");
}
if (!rvItem.DifferentStoresImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling av " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.DifferentStoresImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.DifferentStoresText + "</div>");
}
if (!rvItem.SubCategoryImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling på " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.SubCategoryImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.SubCategoryText + "</div>");
}
if (!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1>" + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.TransactionsPerMonthImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.TransactionsPerMonthText + "</div>");
}
if (!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Antall transaksjoner pr. mnd på " + mainCatName + "</h1>");
html.Append("<img class='report-img img-size' src='" + rvItem.AverageTradeValueImage + "'></img>");
html.Append("<div class='text-block break'>" + rvItem.AverageTradeValueText + "</div>");
}
}
//---------------------------- Inntekter title -----------------------------------------------------------//
imageArray = System.IO.File.ReadAllBytes(HttpContext.Server.MapPath("../Content/images/inntekter_header.png"));
base64ImageRepresentation = Convert.ToBase64String(imageArray);
html.Append(header);
html.Append("<div class='break' style='display:block;padding-top:300px;padding-left:0px;'>");
html.Append("<img class='report-img' src='data:image/png;base64," + base64ImageRepresentation + "'></img>");
html.Append("</div>");
//---------------------------------------- Inntekt per måned (p50) --------------------------------------------------------------//
if (await db.InntektPerMonths.AnyAsync(n => n.CustomerId == customerId))
{
var innPerManed = await db.InntektPerMonths.FirstAsync(n => n.CustomerId == customerId);
if (!innPerManed.InntektPerMonthImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Inntekt per måned </h1>");
html.Append("<img class='report-img img-size' src='" + innPerManed.InntektPerMonthImage + "'></img>");
html.Append("<div class='text-block break'>" + innPerManed.Text + "</div>");
}
}
//---------------------------------------- Fordeling av inntekt (p51) --------------------------------------------------------------//
if (await db.InntektFordelings.AnyAsync(n => n.CustomerId == customerId))
{
var innFordelings = await db.InntektSubCategories.FirstAsync(n => n.CustomerId == customerId);
if (!innFordelings.InntektSubCategoryImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling av inntekt </h1>");
html.Append("<img class='report-img img-size' src='" + innFordelings.InntektSubCategoryImage + "'></img>");
html.Append("<div class='text-block break'>" + innFordelings.Text + "</div>");
}
}
//---------------------------------------- Fordeling mellom inntekt og forbruk (p52) --------------------------------------------------------------//
if (await db.InntektSubCategories.AnyAsync(n => n.CustomerId == customerId))
{
var innSub = await db.InntektFordelings.FirstAsync(n => n.CustomerId == customerId);
if (!innSub.InntektFordelingImage.IsNullOrWhiteSpace())
{
html.Append(header);
html.Append("<h1> Fordeling mellom inntekt og forbruk </h1>");
html.Append("<img class='report-img img-size' src='" + innSub.InntektFordelingImage + "'></img>");
html.Append("<div class='text-block break'>" + innSub.Text + "</div>");
}
}
html.Append("</body></html>");
//---------- some code
//........
var htmlStr = html.toString();
字符串在。net中是不可变的。您应该使用StringBuilder而不是字符串。
像这样:
StringBuilder sb = new StringBuilder(16 * 1024); // adapt memory reservation depending on document size
sb.Append("string_to_append");
...
return sb.ToString();
此技术远比+=有效
您可以做以下事情来提高性能:
- 尽量减少在StringBuilder实例上使用Append()，并以链方式调用它。这可能会导致对追加方法的更多调用。但这要看情况。你需要检查并决定什么是最适合你的。
- 将
var替换为除anonymous类型外的准确类型 - 不要使用
async/await(删除并观察是否影响性能)，因为我认为这对您的情况没有好处。(可选) 将变量移出
foreach循环。示例:
html.Append("<div class='break' style='margin: 0 0 0 0px;'><img class='report-img' style='margin:150px 0 400px 0;' src='data:image/jpeg;base64,")
.Append(base64ImageRepresentation)
.Append("</img><p class='purpleText' style='color:#8E0033; font-size: 30px; margin:0; line-height:20px;'>")
.Append( title)
.Append("</p><p class='purpleText' style='color:#8E0033; font-size: 16px;'>")
.Append( bigTitle)
.Append("</p><p style='color:#980000; font-size: 50px; margin: 46px 0 0 0;'>")
.Append(custName.firstName)
.Append("<span>")
.Append(custName.lastName)
.Append("</span></p></div>");
var示例:var tranCatIdBruk = 0;到int tranCatIdBruk = 0;
你在foreach循环中使用了var关键字，这会导致性能下降(相信我，我已经做了实际操作，当时我很震惊:D)
foreach之外的变量示例:
var mainCatName; // Replace var with string.
foreach (var rvItem in rvBrukItems)
{
mainCatName = (await db.Categories.FirstAsync(n => n.Id == rvItem.MainCategoryId)).Name;
if (!rvItem.TotalPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.DifferentStoresImage.IsNullOrWhiteSpace() ||
!rvItem.SubCategoryImage.IsNullOrWhiteSpace() ||
!rvItem.TransactionsPerMonthImage.IsNullOrWhiteSpace() ||
!rvItem.AverageTradeValueImage.IsNullOrWhiteSpace())
{
html.Append("<li class='list-level2'>" + mainCatName + "</li>");
}
}
对于StringBuilder Append()，你可以参考下面的链接，它解释了内部发生的事情:
String Builder最佳实践
希望这对你有帮助。如果您有任何疑问，请告诉我。