CODEWITHSUNDEEP
c#entity-frameworklinq
LAffair
LAffair

Reputation: 1998

Linq simple query improvement

I'm a beginner in Linq queries and I'm wondering if my query can be improved one way ore another:

long vehid = json.VehicleId.Value;
DateTime date = DateTime.Parse(json.date.Value);

var Alerts = (from t2 in entities.Alerts.AsNoTracking()
              where 
                 t2.GeneratedTimeLocal.Year == date.Year
              && t2.GeneratedTimeLocal.Month == date.Month
              && t2.GeneratedTimeLocal.Day == date.Day
              && (t2.AlertType == 2 || t2.AlertType == 3)
              && t2.vId == vid
              select new
              {
                  GeneratedTimeLocal = t2.GeneratedTimeLocal,
                  OptionalText = t2.OptionalText
              });

return Alerts;

The problem is that the Alerts datatable has a huge amount of data in it that increases day by day and right now it's kind of slow. The GeneratedTimeLocal field from Alerts datatable is type datetimeoffset(7). Is there a way to improve this query?

Upvotes: 4

Views: 94

Answers (1)

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13787

Define a date range to improve the query. Then check the query execution plan and based on that decide if you need a new index or change existing indexes. 

long vehid = json.VehicleId.Value;
DateTime dateFrom = DateTime.Parse(json.date.Value).Date; // date with no time
DateTime dateTo = dateFrom.AddDays(1); // add one day to create the date range

var Alerts = (from t2 in entities.Alerts.AsNoTracking()
              where 
                 t2.GeneratedTimeLocal >= dateFrom 
              && t2.GeneratedTimeLocal <= dateTo
              && (t2.AlertType == 2 || t2.AlertType == 3)
              && t2.vId == vid
              select new
              {
                  GeneratedTimeLocal = t2.GeneratedTimeLocal,
                  OptionalText = t2.OptionalText
              });

return Alerts;

On the other hand, remember that this query won't be executed until you do a ToList(), for example.

Try this index:

CREATE INDEX IX_Alert_GeneratedTimeLocal_vId_AlertType_with_include ON Alert(GeneratedTimeLocal, vId, AlertType) INCLUDE(OptionalText)

I'm assuming you're using SQL Server. You could also try a filtered index if the table is huge. Check out this link: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

Upvotes: 3

Related Questions