CODEWITHSUNDEEP
c#azureentity-frameworklinq
Cornel
Cornel

Reputation: 4709

Improve Linq SQL query performance

I have the following LINQ query that runs on a 1 million records table:

var result = await lmsDb.SendGridEvents
 .Where(s => s.InternalId == internalId && s.Email.ToLower() == email.ToLower())
 .Select(s => new MailEventDTO
 {
   InternalId = s.InternalId,
   Email = s.Email,
   error = s.error,
   Event = s.Event,
   Reason = s.Reason,
   Response = s.Response,
   Url = s.Url,
   TimeStamp = s.TimeStamp
  })
  .OrderByDescending(a => a.TimeStamp) // get the latest
  .FirstOrDefaultAsync();

return result;

How can I improve the performance of this query? Started to become really really slow.

Upvotes: 1

Views: 183

Answers (1)

Steve Py
Steve Py

Reputation: 34698

Check the collation setting on your database. If it is _CI_ this will perform case insensitive string comparisons so you do not need to perform explicit case conversions. This will allow SQL Server to utilize indexes for the Email address. If it's _CS_ this is case sensitive which will be a crimper on your performance. In that case pre-case the variable and just apply the ToLower on the entity value in the expression.

The next step would be to look at the database advisor for Azure SQL in the environments that you are experiencing the performance issues, typically in production. This will give you an overall view of the performance of the database including providing suggestions for index changes based on the types of queries running.

For SQL Server I like to capture the actual queries being run using a profiler, then execute these individually to inspect the execution plan being used, as well as look for any index suggestions. For instance at a guess for this query you would likely want an index on the combination of InternalId ASC, Email ASC, and Timestamp DESC. If you are using a _CS_ collation then I suspect Email might be better off not in the index, but ultimately base the index addition/change/deletions off the suggestions from the advisor. When it comes to index creation, look at the suggestions from the tooling as creating the wrong indexes just lead to storage bloat and performance costs with no benefit.

This should give you a few initial places to start looking into.

Upvotes: 1

Related Questions