对ASP.NET MVC中的LINQ查询性能进行故障排除 [英] Troubleshooting LINQ Query Performance in ASP.NET MVC
问题描述
在将新表连接到LINQ查询时遇到问题.实际上，它返回我期望的数据，并且在测试中运行速度很快.但是，似乎随着更多用户连接到数据库，查询开始超时.例如，在生产的前30或45分钟内，一切工作正常，但随后在大约8:20 AM，它开始超时.再次，我认为这是由于整体上数据库使用量的增加.
I'm having trouble with a LINQ query after joining a new table to it. Actually, it returns the data I'm expecting and it runs fast in testing. However, it seems that, as more users connect to the database, the query begins to timeout. For example, everything was working fine for the first 30 or 45 minutes in Production, but then at about 8:20 AM, it started to timeout. Again, I assume this is due to increased usage of the database on the whole.
如果有帮助，以下是ASP.NET MVC(5)应用程序的一些背景知识.
Here is a little background on the ASP.NET MVC (5) application, in case that helps.
- 用户向我们的诊所提交了转诊
- 引荐包含一个或多个订单
- 如果提供的人员信息与现有人员不匹配，那么我会做几件事，包括在订单"表中插入记录(对于引荐所选择的每个订单，一条记录).
- 如果提供的人员信息确实与我们系统中的现有人员匹配，那么我会将引用保留"在队列中，直到通过将其与现有人员匹配或覆盖它并在其中创建新人员来手动解决该推荐为止系统.此时，将在表格中创建在推介中选择的所有订单.
因此，在这种情况下要考虑的两个主要表是引荐"(在我的代码中称为"Referrals")和"order"(在我的代码中称为"ReferralPPA")表.到现在为止，我还不需要将相关查询从Referrals表链接到ReferralPPA表(将查询链接到ReferralPPAs表似乎是一旦数据库/应用程序使用率增加，就会使查询速度变慢).
So, the two main tables to think about in this scenario are the "referral" (named "Referrals" in my code) and "order" (named "ReferralPPAs" in my code) tables. Until now, I have not needed to link the query in question from the Referrals table to the ReferralPPAs table (linking the query to the ReferralPPAs table seems to be what is slowing the query down once database/application usage increases).
此外，如果这有帮助，则引荐由外部用户输入，而我从引荐创建的订单在单独的应用程序中使用，内部人员作为用户使用，尽管它们都在同一个数据库中. ReferralPPAs表可能在一天中的大部分时间内都被大量使用.
Also, in case this helps, the referrals are entered by external users, while the orders I created from the referral are worked in a separate application with internal staff as the users, though it's all in the same database. The ReferralPPAs table is probably being used pretty heavily most of the day.
查询如下:
IQueryable<ReferralListViewModel> referrals = (from r in _context.Referrals
join cu in _context.ClinicUsers on r.ClinicId equals cu.ClinicId
/* Here is the seemingly problematic join */
from ppa in _context.ReferralPPAs
.Where(p => p.ref_id == r.seq_no.ToString())
.DefaultIfEmpty()
/* End of seemingly problematic join */
join ec in _context.EnrolledClinics on r.ClinicId equals ec.ClinicId
join pm in _context.ProviderMasters on ec.ClinicId equals pm.ClinicId
join ml in _context.MasterLists on pm.HealthSystemGuid equals ml.Id
join au in _context.Users on r.ApplicationUserId equals au.Id
where cu.UserId == userId
select new ReferralListViewModel()
{
ClinicName = pm.Description,
ClinicId = r.ClinicId,
ReferralId = r.seq_no,
EnteredBy = (au.FirstName ?? string.Empty) + " " + (au.LastName ?? string.Empty),
PatientName = (r.LastName ?? string.Empty) + ", " + (r.FirstName ?? string.Empty),
DateEntered = r.create_timestamp,
Status = ppa != null ? ppa.Status : string.Empty
});
因此，如果没有上面我提到的联接，我不会遇到任何问题，并且运行速度非常快.同样，添加联接似乎也很快，直到系统上有一定数量的用户为止(至少这是我的假设).
So, without the join I make reference to above, I experience no problems and it runs quite fast. Adding the join also appears to be fast, again, until a certain number of users are on the system (at least that's my assumption).
我尝试了其他几项措施，以帮助提高性能并防止出现问题.我将UseDatabaseNullSemantics设置为True，这似乎对整体性能产生了很大的影响.
A couple of other things I've tried to help improve performance and prevent the problem. I set the UseDatabaseNullSemantics to True, which seems to make a big difference in the overall performace.
_context.Configuration.UseDatabaseNullSemantics = true;
我还想知道问题是否出在有关表的锁定上，所以我尝试将查询包装在事务中以执行ReadUncommitted.
I also wondered if the problem was an issue of locking on the table in question, so I tried wrapping the query in a transaction to do a ReadUncommitted.
using (var transaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
//query
}
同样，虽然这可以整体提高性能，但似乎并不能最终解决问题.
Again, while this improves the overall performance a little bit, it didn't seem to ultimately resolve the problem.
如果有人对如何解决这个问题有任何想法，想法或建议，我将不胜感激.
If anyone has any thoughts, ideas, or suggestions on how to tackle this, I would greatly appreciate it.
推荐答案
基于注释中的其他信息，看起来像联接条件中的Guid到String转换
Based on the additional information from the comments, looks like the Guid to String conversion in the join condition
p.ref_id == r.seq_no.ToString()
翻译为
t1.ref_id = LOWER(CAST(t2.seq_no AS nvarchar(max))))
使查询不可存储，而隐式进行SqlServer转换
makes the query not sargable, while the implicit SqlServer conversion
t1.ref_id = t2.seq_no
工作正常.
所以问题是如何删除该演员表.没有选项，查询表达式树也不允许删除它.如果SqlServer提供程序sql生成器正在执行该优化，那将是很好的选择，但实际上并没有，并且没有简单的方法可以挂接到它.
So the question is how to remove that cast. There is no option for that and also query expression tree does not allow removing it. It would be nice if the SqlServer provider sql generator was doing that optimization, but it doesn't and there is no easy way to hook into it.
作为解决方法，我可以提供以下解决方案.它使用自定义 DbExpressionVisitor 修改
As a workaround I can offer the following solution. It uses a custom IDbCommandTreeInterceptor and DbExpressionVisitor to modify the DbCommandTree of the query.
这是拦截代码:
using System;
using System.Data.Entity.Core.Common.CommandTrees;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure.Interception;
using System.Linq.Expressions;
using System.Reflection;
namespace EFHacks
{
public class MyDbCommandTreeInterceptor : IDbCommandTreeInterceptor
{
public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
{
if (interceptionContext.OriginalResult.DataSpace != DataSpace.SSpace) return;
var queryCommand = interceptionContext.Result as DbQueryCommandTree;
if (queryCommand != null)
{
var newQuery = queryCommand.Query.Accept(new GuidToStringComparisonRewriter());
if (newQuery != queryCommand.Query)
{
interceptionContext.Result = new DbQueryCommandTree(
queryCommand.MetadataWorkspace,
queryCommand.DataSpace,
newQuery);
}
}
}
}
class GuidToStringComparisonRewriter : DefaultExpressionVisitor
{
public override DbExpression Visit(DbComparisonExpression expression)
{
if (IsString(expression.Left.ResultType) && IsString(expression.Right.ResultType))
{
var left = expression.Left;
var right = expression.Right;
if (RemoveCast(ref right) || RemoveCast(ref left))
return CreateComparison(expression.ExpressionKind, left, right);
}
return base.Visit(expression);
}
static bool IsGuid(TypeUsage type)
{
var pt = type.EdmType as PrimitiveType;
return pt != null && pt.PrimitiveTypeKind == PrimitiveTypeKind.Guid;
}
static bool IsString(TypeUsage type)
{
var pt = type.EdmType as PrimitiveType;
return pt != null && pt.PrimitiveTypeKind == PrimitiveTypeKind.String;
}
static bool RemoveCast(ref DbExpression expr)
{
var funcExpr = expr as DbFunctionExpression;
if (funcExpr != null &&
funcExpr.Function.BuiltInTypeKind == BuiltInTypeKind.EdmFunction &&
funcExpr.Function.FullName == "Edm.ToLower" &&
funcExpr.Arguments.Count == 1)
{
var castExpr = funcExpr.Arguments[0] as DbCastExpression;
if (castExpr != null && IsGuid(castExpr.Argument.ResultType))
{
expr = castExpr.Argument;
return true;
}
}
return false;
}
static readonly Func<DbExpressionKind, DbExpression, DbExpression, DbComparisonExpression> CreateComparison = BuildCreateComparisonFunc();
static Func<DbExpressionKind, DbExpression, DbExpression, DbComparisonExpression> BuildCreateComparisonFunc()
{
var kind = Expression.Parameter(typeof(DbExpressionKind), "kind");
var booleanResultType = Expression.Field(null, typeof(DbExpressionBuilder), "_booleanType");
var left = Expression.Parameter(typeof(DbExpression), "left");
var right = Expression.Parameter(typeof(DbExpression), "right");
var result = Expression.New(
typeof(DbComparisonExpression).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null,
new[] { kind.Type, booleanResultType.Type, left.Type, right.Type }, null),
kind, booleanResultType, left, right);
var expr = Expression.Lambda<Func<DbExpressionKind, DbExpression, DbExpression, DbComparisonExpression>>(
result, kind, left, right);
return expr.Compile();
}
}
}
和DbConfiguration进行安装:
class MyDbConfiguration : DbConfiguration
{
public MyDbConfiguration()
{
AddInterceptor(new EFHacks.MyDbCommandTreeInterceptor());
}
}
已经在SqlServer数据库的EF6.1.3和EF6.2中进行了测试，并且可以正常工作.
Tested and working in EF6.1.3 and EF6.2 with SqlServer database.
但是请小心使用.
首先，它仅适用于SqlServer.
First, it works only for SqlServer.
第二，这有点棘手，因为我不得不使用内部字段和内部类构造函数来跳过对相等类型的比较操作操作数的检查.因此，将来某些EF6更新可能会破坏它.
Second, it's hackish because I had to use internal field and internal class constructor in order to skip the check for equal types of the comparison operation operands. So some future EF6 update might break it.
这篇关于对ASP.NET MVC中的LINQ查询性能进行故障排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
