Troubleshooting LINQ Query Performance in ASP.NET MVC

Question

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.

Here is a little background on the ASP.NET MVC (5) application, in case that helps.

• A user submits a referral to our clinic
• The referral contains one or more orders
• If the person information supplied does not match an existing person, I do several things, including inserting records in an "orders" table (one record for each order selected on the referral).
• If the person information supplied does match an existing person in our system, then I "hold" the referral in a queue until it is manually resolved by either matching it to an existing person or by overriding it and creating a new person in the system. At this time, any orders selected in the referral are created in the table.

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).

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.

The query looks like this:

            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).

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;

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.

Answer 1

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()

translated to

t1.ref_id = LOWER(CAST(t2.seq_no AS nvarchar(max))))

makes the query not sargable, while the implicit SqlServer conversion

t1.ref_id = t2.seq_no

works just fine.

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.

As a workaround I can offer the following solution. It uses a custom IDbCommandTreeInterceptor and DbExpressionVisitor to modify the DbCommandTree of the query.

Here is the interception code:

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();
        }
    }
}

and DbConfiguration to install it:

class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        AddInterceptor(new EFHacks.MyDbCommandTreeInterceptor());
    }
}

Tested and working in EF6.1.3 and EF6.2 with SqlServer database.

But use it with care.

First, it works only for SqlServer.

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.