CODEWITHSUNDEEP
c#linqentity-frameworksqlite
Cody Hicks
Cody Hicks

Reputation: 420

sqlite query slow, how to optimize (using linq to entities)

I have used MS SQL server a few times and have yet to run into a speed issue when querying, using linq to entities. This time, I am using sqlite, as to ship the entire database with the application.

I have a winforms app that contains 4 search fields. My goal is to design the search in such a way that the results reflect a single field or multiple fields (builds a query based on which fields have a search term).

Currently, my query works but takes a considerable amount of time to perform against an sqlite database. Particularly, on the first run. I assume this is because sqlite doesn't have a powerful server behind it and the results are processed locally and loaded into memory. I'm thinking the database is indexing its self or has to build some sort of cache the first time.

How can I optimize my linq query to sqilte where I'm not necessarily loading the entire table into memory and then constraining results but constraining results as the table is being loaded?

    public List<ResultGridviewModel> GetChartsFromSearch(string patientID, string firstName, string lastName, DateTime? dateOfBirth)
    {
        using (var _dataContext = new dbEntities())
        {

            var records = (from c in _dataContext.charts
                          select new ResultGridviewModel 
                          {
                              AltID = c.AltID,
                              FirstName = c.FirstName,
                              LastName = c.LastName,
                              DateOfBirth = c.DateOfBirth,
                              Description = c.Description,
                              ServiceDateTime = c.ServiceDateTime
                          });


            // AltID (PatientID)
            if (!string.IsNullOrEmpty(patientID))
            {
                records = records.Where(x => x.AltID.Contains(patientID.Trim().ToUpper()));
            }

            // First Name
            if (!string.IsNullOrEmpty(firstName))
            {
                records = records.Where(x => x.FirstName.Contains(firstName.Trim().ToUpper()));
            }

            // Last Name
            if (!string.IsNullOrEmpty(lastName))
            {
                records = records.Where(x => x.LastName.Contains(lastName.Trim().ToUpper()));
            }

            // Date Of Birth
            if (dateOfBirth != null)
            {
                records = records.Where(x => x.DateOfBirth == dateOfBirth);
            }


            return records.ToList();
        }
    }

I have applied indexes to these fields on the database its self but I feel the issue lies within my query. Any Suggestions on refactoring for optimization?

The Database is ~350k records as of now and could grow larger. Eventually, I will stop adding records to it but lets assume for a rough estimate that it will have ~700k records

Upvotes: 1

Views: 1112

Answers (1)

Aducci
Aducci

Reputation: 26664

The biggest optimization would be changing the Contains to a StartsWith. Which would be equivalent to changing from name like '%search%' to name like 'search%'. Otherwise SQLite can't fully use the indexes you placed on the columns and you are basically searching the whole table.

// First Name
if (!string.IsNullOrEmpty(firstName))
{
  firstName = firstName.Trim().ToUpper();
  records = records.Where(x => x.FirstName.StartsWith(firstName));
}

Upvotes: 3

Related Questions