How can I speed up my Entity Framework LINQ Query?

Question

Sorry for such a generic question.. I am using Entity & LINQ to query a table (Model) called Patient. My goal is use JqueryUI's autocomplete to assist in an ASP.Net MVC 5 suggested search text bar in which you may use a string for patient name, but also their Key in the same box. Here is my Model:

public partial class Patient
{
    public Patient()
    {
        PickupExchange = new HashSet<PickupExchange>();
        SalesOrder2 = new HashSet<SalesOrder2>();
    }

    public int PtKey { get; set; }
    public int? PatientId { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string BillingAddress1 { get; set; }
    public string BillingAddress2 { get; set; }
    public string BillingCity { get; set; }
    public string BillingState { get; set; }
    public string BillingZip { get; set; }
    public string BillingPhone { get; set; }
    public string Faxnumber { get; set; }
    public string EmailAddress { get; set; }
    public string MobilePhone { get; set; }
    public string Suffix { get; set; }
    public DateTime? Dob { get; set; }
    public string Ssn { get; set; }
    public string HoldAcct { get; set; }
    public string AccountNumber { get; set; }
    public string AccountGrp { get; set; }
    public string Sex { get; set; }
    public decimal? Height { get; set; }
    public decimal? Weight { get; set; }
    public string MaritalStatus { get; set; }
    public string Employment { get; set; }
    public byte? DiscountPct { get; set; }
    public byte? BranchKey { get; set; }
    public string DelivAddress1 { get; set; }
    public string DelivAddress2 { get; set; }
    public string DelivCity { get; set; }
    public string DelivState { get; set; }
    public string DelivZip { get; set; }
    public string DelivPhone { get; set; }
    public string User1 { get; set; }
    public string User2 { get; set; }
    public string User3 { get; set; }
    public string User4 { get; set; }
    public string EclastName { get; set; }
    public string EcfirstName { get; set; }
    public string EcmiddleName { get; set; }
    public string Ecaddress1 { get; set; }
    public string Ecaddress2 { get; set; }
    public string Eccity { get; set; }
    public string Ecstate { get; set; }
    public string EcpostalCode { get; set; }
    public string EcphoneNum { get; set; }
    public string Ecfax { get; set; }
    public string Ecemail { get; set; }
    public string Rprelationship { get; set; }
    public string RplastName { get; set; }
    public string RpfirstName { get; set; }
    public string RpmiddleName { get; set; }
    public string Rpaddress1 { get; set; }
    public string Rpaddress2 { get; set; }
    public string Rpcity { get; set; }
    public string Rpstate { get; set; }
    public string RppostalCode { get; set; }
    public string RpphoneNum { get; set; }
    public string Rpfax { get; set; }
    public string Rpemail { get; set; }
    public string HippasignatureOnFile { get; set; }
    public string TaxZone { get; set; }
    public string BillingCounty { get; set; }
    public string DeliveryCounty { get; set; }
    public int? ReferralKey { get; set; }
    public string ReferralType { get; set; }
    public short? CreatedBy { get; set; }
    public DateTime? CreateDt { get; set; }
    public string NickName { get; set; }
    public string AutoChargeCc { get; set; }
    public string SarautoPayStatus { get; set; }
    public string SareDeliveryStatus { get; set; }
    public string SarpaymentPlan { get; set; }
    public string Sarinformation { get; set; }
    public DateTime UpdateDt { get; set; }
    public DateTime? Dod { get; set; }
}

The problem is my SQL Table containing all the Patient Data is absolutely huge. I am using my LINQ query to return a TOP 10 as json back to my javasript function:

 var alreadyRunning = false;
 patientID.bind('input', function () {
    if (alreadyRunning == false) {
        alreadyRunning = true;
        $.ajax({
            type: 'POST',
            url: '/Controller/Model',
            dataType: 'json',
            data: {
                patientID: patientID.val()
            },
            success(data) {
                patientID.autocomplete({
                    source: data
                });
                alreadyRunning = false;
            }
            , failure(data) {
                alert("Error Searching Value: " + patientID.val());
                alreadyRunning = false;
            }
            , error(data) {
                alert("Error Searching Value: " + patientID.val());
                alreadyRunning = false;
            }
        });
    }
    else {
        patientID.autocomplete({
            source: ["None"]
        });
    }

This LINQ query keeps timing out and I can't find a way to speed up the query.. am I doing something wrong or is my data set just too large?

  ptTable = int.TryParse(patientID, out number) ?
        btd.Patient.Where(x => x.Database == "databaseName" && x.PatientId.ToString().StartsWith(number.ToString())).OrderBy(x => x.LastName).Take(10).ToList() :
        btd.Patient.Where(x => x.Database == "databaseName" && x.LastName.ToLower() + ", " + x.FirstName.ToLower()).StartsWith(patientID.ToLower()).OrderBy(x => x.LastName).Take(10).ToList();    

Is it too much string manipulation for SQL to execute quickly? Any advice would be very beneficial!

Answer 1

Apparently you're looking for a way to create an Autocomplete but the query is too slow, and on top of that you have no control over the database. In that case you should store all the names and patient numbers in memory even if you have a large set of data you should still be able to cache this fine.

var lookupData = context.Patients.Select(p =>new { p.LastName, p.patientId}).ToArray();

var lastNames = lookupData.Select(x => x.LastName).ToArray();
var patientId = lookupData.Where(x => null != x.patientId).ToArray(); 

once you have these collection's you can just filter on this:

This way you only have to make a query once, you will however have to add a mechantism to update the cache when adding/removing/updating a patient, but atleast you will only have to do this query once

Answer 2

Problem with SQL (MySQL) server is that it stores data in rows. It serializes each row of data and assigns internal row ids. Something like this (from wikipedia):

001:10,Smith,Joe,40000;
002:12,Jones,Mary,50000;
003:11,Johnson,Cathy,44000;
004:22,Jones,Bob,55000;

In your case each row will contain lot of columns. Row-based systems are good for returning whole row of data. But they are not efficient when you need to filter data, because whole row should be loaded from disc into memory, then required fields can be analyzed. And row-based system will do that for whole table.

You can boost performance by creating indexes for columns which you are using in query. E.g. for Database field (and other fields probably).

ALTER TABLE Patient ADD INDEX COLUMN_NAME (COLUMN_NAME)

In that case (again from wiki) index for some column will look like

001:Smith;
003:Jones;
002:Johnson;
004:Jones;

So instead of loading all row from disc, database will load only index data. That is much much faster.

If performace still will suck after you will create indexes, consider to use column-oriented database.