Troubleshooting LINQ Query Performance in ASP.NET MVC

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.